from rest_framework.views import APIView
from rest_framework.response import Response
from django_redis import get_redis_connection
from random import randint
from rest_framework import status
import logging, datetime, jwt, pymysql, hashlib, requests, time, json, re, xlrd, math, rsa
from django.utils import timezone
from django.conf import settings
from rest_framework.viewsets import ModelViewSet
from rest_framework import filters
from django_filters.rest_framework import DjangoFilterBackend
from django.shortcuts import render
from django.http import StreamingHttpResponse
from django.utils.encoding import escape_uri_path
from django.db.models import Q, F, Func
from django.db import transaction
from elasticsearch import Elasticsearch
from django.db import connection
from django.db.models.expressions import RawSQL
from geopy.distance import geodesic
from PIL import Image
import io

from equity_admin.serializers import *
from equity_mall.utils.common_msg import common_msg
from equity_admin.filters import *
from equity_mall.utils.utils import get_level_list, random_str, get_sign, ryx_profitsharing_order, get_sign_HMACSHA1
from equity_mall.utils.qiniu_storage import pic_upload, pic_delete
from equity_mall.utils.sync_img_to_local import sync_img_to_local, make_qrcode, make_qrcode2
from equity_admin.tasks import *
from equity_admin.utils import get_stockinfo, get_alidiscountcoupon
from customers.utils import AlipayOpen as C_AlipayOpen
from equity_mall.utils.gzh_send_templates import ali_fundtrans_template
from equity_mall.celery import celery_app
from equity_mall.utils.pages import MyPage
from equity_mall.utils.express_utils import KuaiDi100

logger = logging.getLogger('django')


class YSHAreaCodeModelViewSet(ModelViewSet):

    queryset =  YSHAreaCode.objects.all().order_by('id')
    serializer_class = YSHAreaCodeModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name',)


class YSHContractModelViewSet(ModelViewSet):

    queryset =  YSHContract.objects.filter(is_delete=0)
    serializer_class = YSHContractModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('supplierName',)


class AfterSaleApplicationFormModelViewSet(ModelViewSet):

    pagination_class = MyPage

    def get_queryset(self):
        user = self.request.iser
        queryset = AfterSaleApplicationForm.objects.filter().exclude(form_status=4)
        return queryset

    def get_serializer_class(self):
        return AfterSaleApplicationFormModelSerializer

    def update(self, request, *args, **kwargs):
        # user = self.request.iser
        # instance = self.get_object()
        # if instance.form_status != 1:
        #     return Response({'error': '申请单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        # audit_status = self.request.data.get('audit_status', None)
        # return_msg = self.request.data.get('return_msg', None)
        # order = instance.order
        # if order.state != 5:
        #     return Response({'error': '订单单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        # if not audit_status:
        #     return Response({'error': '请上传审核参数'}, status=status.HTTP_400_BAD_REQUEST)
        # if str(audit_status) == '2':
        #     url = 'https://api.gdwxyf.com/api/refund.do'
        #     datetime_now = datetime.datetime.now()
        #     order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
        #     body = {
        #         "program_id": "202316837141378884",
        #         "shop_id": order.merchant.ruiyinxinMerchantID,
        #         "refund_order_id": order_id,
        #         "order_id": order.orderNum
        #     }
        #     key = '6715C7D4435343BA0459EAEC2334D81B'
        #     sign = get_sign(body, key)
        #     body['sign'] = sign
        #     response = requests.post(url=url, json=body)
        #     result = response.json()
        #     if result['result_code'] == 'success':
        #         refund_order_data = {}
        #         refund_order_data['order_id'] = order.id
        #         refund_order_data['user_id'] = user.id
        #         refund_order_data['operator_role'] = 1
        #         refund_order_data['refund_order_id'] = order_id
        #         refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
        #         refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
        #         refund_order_data['refund_money'] = result['refund_money']
        #         refund_order_data['refund_procedure_money'] = result['commission']
        #         refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
        #         refund_order_data['remark'] = '商户同意退款'
        #         RefundOrderInfo.objects.create(**refund_order_data)
        #         order.state = 8
        #         # order.refund_money += Decimal(result['refund_money'])
        #         order.refund_money = order.realpay_amount
        #         order.out_refund_no = order_id
        #         order.refund_id = result['refund_transaction_id']
        #         order.save()
        #         instance.form_status = 2
        #         # instance.refund_money = result['refund_money']
        #         instance.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
        #         instance.save()
        #         try:
        #             aftersale_applyment_send.delay({'id': instance.id})
        #         except:
        #             pass
        #         return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
        #     else:
        #         refund_order_data = {}
        #         refund_order_data['refund_status'] = 2
        #         refund_order_data['operator_role'] = 1
        #         refund_order_data['order_id'] = order.id
        #         refund_order_data['user_id'] = user.id
        #         refund_order_data['return_msg'] = result['error_msg']
        #         refund_order_data['refund_order_id'] = order_id
        #         refund_order_data['refund_time'] = datetime_now
        #         refund_order_data['remark'] = '商户同意退款'
        #         RefundOrderInfo.objects.create(**refund_order_data)
        #         instance.form_status = 3
        #         instance.return_msg = result['error_msg']
        #         instance.save()
        #         try:
        #             aftersale_applyment_send.delay({'id': instance.id})
        #         except:
        #             pass
        #         return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)
        # else:
        #     if not return_msg:
        #         return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
        #     order.state = instance.order_status
        #     order.save()
        #     instance.form_status = 3
        #     instance.return_msg = return_msg
        #     instance.save()
        #     try:
        #         aftersale_applyment_send.delay({'id': instance.id})
        #     except:
        #         pass
        #     return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        user = self.request.iser
        instance = self.get_object()
        if instance.form_status not in [1, 8]:
            return Response({'error': '申请单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        audit_status = self.request.data.get('audit_status', None)
        return_msg = self.request.data.get('return_msg', None)
        order = instance.order
        if order.state != 5:
            return Response({'error': '订单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        if not audit_status:
            return Response({'error': '请上传审核参数'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.form_status == 8:
            if instance.reason_type == 1:
                if str(audit_status) == '2':
                    url = 'https://api.gdwxyf.com/api/refund.do'
                    datetime_now = datetime.datetime.now()
                    order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                    body = {
                        "program_id": "202316837141378884",
                        "shop_id": order.merchant.ruiyinxinMerchantID,
                        "refund_order_id": order_id,
                        "order_id": order.orderNum
                    }
                    key = '6715C7D4435343BA0459EAEC2334D81B'
                    sign = get_sign(body, key)
                    body['sign'] = sign
                    response = requests.post(url=url, json=body)
                    result = response.json()
                    if result['result_code'] == 'success':
                        refund_order_data = {}
                        refund_order_data['order_id'] = order.id
                        refund_order_data['user_id'] = user.id
                        refund_order_data['operator_role'] = 4
                        refund_order_data['refund_order_id'] = order_id
                        refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
                        refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
                        refund_order_data['refund_money'] = result['refund_money']
                        refund_order_data['refund_procedure_money'] = result['commission']
                        refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                        refund_order_data['remark'] = '管理员同意退款'
                        RefundOrderInfo.objects.create(**refund_order_data)
                        order.state = 8
                        # order.refund_money += Decimal(result['refund_money'])
                        order.refund_money = order.realpay_amount
                        order.out_refund_no = order_id
                        order.refund_id = result['refund_transaction_id']
                        order.save()
                        instance.form_status = 2
                        # instance.refund_money = result['refund_money']
                        instance.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                        instance.save()
                        return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
                    else:
                        refund_order_data = {}
                        refund_order_data['refund_status'] = 2
                        refund_order_data['operator_role'] = 4
                        refund_order_data['order_id'] = order.id
                        refund_order_data['user_id'] = user.id
                        refund_order_data['return_msg'] = result['error_msg']
                        refund_order_data['refund_order_id'] = order_id
                        refund_order_data['refund_time'] = datetime_now
                        refund_order_data['remark'] = '管理员同意退款'
                        RefundOrderInfo.objects.create(**refund_order_data)
                        instance.form_status = 3
                        instance.return_msg = result['error_msg']
                        instance.save()
                        return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)
                else:
                    if not return_msg:
                        return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
                    order.state = instance.order_status
                    order.save()
                    instance.form_status = 11
                    instance.return_msg = return_msg
                    instance.save()
                    OrderAdvance.objects.create(
                        order_id=instance.order_id,
                        status=7,
                        remark='管理员驳回申请',
                        user_id=user.id
                    )
                    return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
            else:
                if str(audit_status) == '2':
                    expired_time = datetime.datetime.now() + datetime.timedelta(days=3)
                    instance.form_status = 5
                    instance.expired_time = expired_time
                    instance.save()
                    return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
                else:
                    if not return_msg:
                        return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
                    order.state = instance.order_status
                    order.save()
                    instance.form_status = 11
                    instance.return_msg = return_msg
                    instance.save()
                    OrderAdvance.objects.create(
                        order_id=instance.order_id,
                        status=7,
                        remark='管理员驳回申请',
                        user_id=user.id
                    )
                    return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        else:
            if str(audit_status) == '2':
                url = 'https://api.gdwxyf.com/api/refund.do'
                datetime_now = datetime.datetime.now()
                order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                body = {
                    "program_id": "202316837141378884",
                    "shop_id": order.merchant.ruiyinxinMerchantID,
                    "refund_order_id": order_id,
                    "order_id": order.orderNum
                }
                key = '6715C7D4435343BA0459EAEC2334D81B'
                sign = get_sign(body, key)
                body['sign'] = sign
                response = requests.post(url=url, json=body)
                result = response.json()
                if result['result_code'] == 'success':
                    refund_order_data = {}
                    refund_order_data['order_id'] = order.id
                    refund_order_data['user_id'] = user.id
                    refund_order_data['operator_role'] = 4
                    refund_order_data['refund_order_id'] = order_id
                    refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
                    refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
                    refund_order_data['refund_money'] = result['refund_money']
                    refund_order_data['refund_procedure_money'] = result['commission']
                    refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    refund_order_data['remark'] = '管理员同意退款'
                    RefundOrderInfo.objects.create(**refund_order_data)
                    order.state = 8
                    # order.refund_money += Decimal(result['refund_money'])
                    order.refund_money = order.realpay_amount
                    order.out_refund_no = order_id
                    order.refund_id = result['refund_transaction_id']
                    order.save()
                    instance.form_status = 2
                    # instance.refund_money = result['refund_money']
                    instance.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    instance.save()
                    return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
                else:
                    refund_order_data = {}
                    refund_order_data['refund_status'] = 2
                    refund_order_data['operator_role'] = 4
                    refund_order_data['order_id'] = order.id
                    refund_order_data['user_id'] = user.id
                    refund_order_data['return_msg'] = result['error_msg']
                    refund_order_data['refund_order_id'] = order_id
                    refund_order_data['refund_time'] = datetime_now
                    refund_order_data['remark'] = '商户同意退款'
                    RefundOrderInfo.objects.create(**refund_order_data)
                    instance.form_status = 3
                    instance.return_msg = result['error_msg']
                    instance.save()
                    return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)
            else:
                if not return_msg:
                    return Response({'error': '请写明拒绝退款原因'}, status=status.HTTP_400_BAD_REQUEST)
                order.state = instance.order_status
                order.save()
                instance.form_status = 11
                instance.return_msg = return_msg
                instance.save()
                OrderAdvance.objects.create(
                    order_id=instance.order_id,
                    status=7,
                    remark='管理员驳回申请',
                    user_id=user.id
                )
                return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class MerchantMonthlyBillModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = MerchantMonthlyBillFilter
    search_fields = ('merchant__name', 'merchant__wyfMerchantID')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        receipts_time_begin = self.request.query_params.get('receipts_time_begin', None)
        receipts_time_end = self.request.query_params.get('receipts_time_end', None)
        level_id = self.request.query_params.get('level', None)
        try:
            if '_mutable' in self.request.query_params.__dict__:
                self.request.query_params.__dict__['_mutable'] = True
        except:
            pass
        if receipts_time_begin:
            try:
                datetime.datetime.strptime(receipts_time_begin, '%Y-%m')
            except:
                raise serializers.ValidationError({'error': '时间格式有误'})
            self.request.query_params['receipts_time_begin'] = f'{receipts_time_begin}-01'
        if receipts_time_end:
            try:
                datetime.datetime.strptime(receipts_time_end, '%Y-%m')
            except:
                raise serializers.ValidationError({'error': '时间格式有误'})
            self.request.query_params['receipts_time_end'] = f'{receipts_time_end}-01'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        queryset = MerchantMonthlyBill.objects.all()
        if level.level == 0:
            pass
        elif level.level == 1:
            queryset = queryset.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = queryset.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = queryset.filter(level__district_id=level.id)
        else:
            queryset = queryset.filter(level__branch_id=level.id)
        return queryset.order_by('-receipts_time', '-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantMonthlyBillListModelSerializer
        else:
            return MerchantMonthlyBillRetrieveModelSerializer


class ExpressModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = ExpressFilter
    search_fields = ('express_number',)

    def get_queryset(self):
        queryset = Express.objects.all().order_by('-id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return ExpressListModelSerializer
        else:
            return ExpressRetrieveModelSerializer

    def export_express(self, request):
        user = self.request.iser
        level = user.level
        deliverystatus = self.request.query_params.get('deliverystatus', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        sign_time_begin = self.request.query_params.get('sign_time_begin', None)
        sign_time_end = self.request.query_params.get('sign_time_end', None)
        search = self.request.query_params.get('search', None)
        condition = 'WHERE id>9'
        if search:
            condition = condition + f' AND express_number LIKE "%{search}%"'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if sign_time_begin:
            condition = condition + f' AND sign_time >= "{sign_time_begin + " 00:00:00"}"'
        if sign_time_end:
            condition = condition + f' AND sign_time <= "{sign_time_end + " 23:59:59"}"'
        if deliverystatus:
            condition = condition + f' AND deliverystatus IN ({deliverystatus})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.express_type_name FROM (SELECT a.*,b.order_id FROM (SELECT id,express_number,deliverystatus,express_type_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,DATE_FORMAT(sign_time,'%Y-%m-%d %H:%i:%S') AS sign_time FROM tb_order_express {condition})AS a LEFT JOIN (SELECT express_id,GROUP_CONCAT(order_id)AS order_id FROM tb_order_express_relevance GROUP BY express_id)AS b ON a.id=b.express_id)AS c LEFT JOIN (SELECT id,express_type_name FROM tb_order_express_type)AS d ON c.express_type_id=d.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT c.*,d.express_type_name FROM (SELECT a.*,b.order_id FROM (SELECT id,express_number,deliverystatus,express_type_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,DATE_FORMAT(sign_time,'%Y-%m-%d %H:%i:%S') AS sign_time FROM tb_order_express)AS a LEFT JOIN (SELECT express_id,GROUP_CONCAT(order_id)AS order_id FROM tb_order_express_relevance GROUP BY express_id)AS b ON a.id=b.express_id)AS c LEFT JOIN (SELECT id,express_type_name FROM tb_order_express_type)AS d ON c.express_type_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '快递公司', '快递单号', '快递状态', '录入时间', '签收时间', '订单id']
        cols = ['id', 'express_type_name', 'express_number', 'deliverystatus', 'create_time', 'sign_time', 'order_id']
        response = StreamingHttpResponse(self.get_express_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('快递信息.csv'))
        return response

    def get_express_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        DELIVERYSTATUS_ENUM = {
            0: '在途',
            1: '揽收',
            2: '疑难',
            3: '已签收',
            4: '签退',
            5: '派件',
            6: '退回',
            7: '转投',
            8: '清关',
            9: '错误',
            14: '拒签'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'deliverystatus':
                    tmp_str += '"%s",' % (DELIVERYSTATUS_ENUM[row[col]])
                elif col in ['express_number']:
                    tmp_str += '\'"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk').replace('\n', '') if row[col] else '')
                elif col in ['order_id']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk').replace(',', '，') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class AliDiscountCouponDistributionPlatformModelViewSet(ModelViewSet):

    queryset = AliDiscountCouponDistributionPlatform.objects.filter(is_visible=1)
    serializer_class = AliDiscountCouponDistributionPlatformModelSerializer


class AliDiscountCouponSyncWYHRecordModelViewSet(ModelViewSet):

    queryset = AliDiscountCouponSyncWYHRecord.objects.all().order_by('-id')
    serializer_class = AliDiscountCouponSyncWYHRecordModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('merchant__wyfMerchantID', 'sn')
    filter_class = AliDiscountCouponSyncWYHRecordFilter


class MerchantEightEightActivityModelViewSet(ModelViewSet):

    queryset = MerchantEightEightActivity.objects.all().order_by('-activity_end_time')
    serializer_class = MerchantEightEightActivityModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('activity_name', )
    filter_class = MerchantEightEightActivityFilter

    def add_eighteightactivity_white_table(self, request):
        activity_id = self.request.data.get('activity_id', None)
        business_no_array = self.request.data.get('business_no_array', None)
        try:
            instance = self.get_queryset().get(id=activity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_status == 3:
            return Response({'error': '活动已结束'}, status=status.HTTP_400_BAD_REQUEST)
        for business_no in business_no_array:
            defaults = {}
            defaults['activity_id'] = activity_id
            defaults['business_no'] = business_no
            defaults['is_win_type'] = 2
            MerchantEightEightWhiteTable.objects.get_or_create(activity_id=activity_id, business_no=business_no, defaults=defaults)
        return Response({'success': '修改成功'}, status=status.HTTP_200_OK)


class MerchantEightEightWhiteTableModelViewSet(ModelViewSet):

    # serializer_class = MerchantEightEightWhiteTableModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('business_no', )

    def get_queryset(self):
        activity_id = self.request.query_params.get('activity_id', None)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity.activity_type == 3 and activity.id != 47:
                queryset = MerchantEightEightActivityOrder.objects.filter(activity_id=activity_id)
            else:
                queryset = MerchantEightEightWhiteTable.objects.filter(activity_id=activity_id)
        else:
            queryset = MerchantEightEightWhiteTable.objects.filter(activity_id=activity_id)
        return queryset

    def get_serializer_class(self):
        activity_id = self.request.query_params.get('activity_id', None)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity.activity_type == 3 and activity.id != 47:
                return MerchantEightEightActivityOrderModelSerializer
            else:
                return MerchantEightEightWhiteTableModelSerializer
        else:
            return MerchantEightEightWhiteTableModelSerializer

    def export_merchantreachwhitetable(self, request):
        activity_id = self.request.query_params.get('activity_id', None)
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if activity_id:
            activity = MerchantEightEightActivity.objects.filter(id=activity_id).first()
            if activity.activity_type == 3 and activity.id != 47:
                sql = f'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id,is_standard,trade_amount,DATE_FORMAT(trade_time,"%Y-%m-%d")AS trade_time FROM tb_merchant_eight_eight_activity_order WHERE activity_id={activity_id})AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
                ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点', '交易日期', '交易金额', '是否达标']
                cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name', 'trade_time', 'trade_amount', 'is_standard']
            else:
                sql = f'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id={activity_id})AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
                ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
                cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        else:
            sql = 'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table)AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
            ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
            cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        try:
            cursor.execute(sql)
        except:
            sql = 'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id=0)AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
            cursor.execute(sql)
        # ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
        # cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_merchantreachwhitetable_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('交易达标商户明细.csv'))
        return response

    def get_merchantreachwhitetable_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        SUBJECT_TYPE_ENUM = {
            1: '小微',
            2: '个体',
            3: '企业',
            4: '其他',
            5: '事业单位'
        }
        IS_STANDARDS_ENUM = {
            0: '否',
            1: '是',
            2: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'subject_type':
                    tmp_str += '"%s",' % (SUBJECT_TYPE_ENUM[row[col]])
                elif col == 'is_standard':
                    tmp_str += '"%s",' % (IS_STANDARDS_ENUM[row[col]])
                elif col == 'trade_amount':
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantEightEightActivityOrderModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('business_no', )

    def get_queryset(self):
        activity_id = self.request.query_params.get('activity_id', None)
        queryset = MerchantEightEightWhiteTable.objects.filter()
        if activity_id:
            queryset = queryset.filter(activity_id=activity_id)
        return queryset

    def get_serializer_class(self):
        return MerchantEightEightWhiteTableModelSerializer

    def export_merchantreachordertable(self, request):
        activity_id = self.request.query_params.get('activity_id', None)
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id={activity_id})AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
        ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
        cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        try:
            cursor.execute(sql)
        except:
            sql = 'SELECT * FROM (SELECT * FROM (SELECT business_no,activity_id FROM tb_merchant_eigth_eight_white_table WHERE activity_id=0)AS a LEFT JOIN (SELECT name,wyfMerchantID,subject_type,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id;'
            cursor.execute(sql)
        # ziduan = ['商户号', '商户名称', '商户类型', '所属市公司', '所属区县', '所属网点']
        # cols = ['business_no', 'name', 'subject_type', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_merchantreachordertable_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('交易达标商户明细.csv'))
        return response

    def get_merchantreachordertable_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        SUBJECT_TYPE_ENUM = {
            1: '小微',
            2: '个体',
            3: '企业',
            4: '其他',
            5: '事业单位'
        }
        IS_STANDARDS_ENUM = {
            0: '否',
            1: '是',
            2: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'subject_type':
                    tmp_str += '"%s",' % (SUBJECT_TYPE_ENUM[row[col]])
                elif col == 'is_standard':
                    tmp_str += '"%s",' % (IS_STANDARDS_ENUM[row[col]])
                elif col == 'trade_amount':
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantEightEightActivityPrizeModelViewSet(ModelViewSet):

    serializer_class = MerchantEightEightActivityPrizeModelSerializer
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = MerchantEightEightActivityPrizeFilter
    queryset = MerchantEightEightActivityPrize.objects.all()

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        count = self.request.data.get('count', None)
        second_count = self.request.data.get('second_count', None)
        instance.count = count
        instance.second_count = second_count
        instance.save()
        return Response({'success': '修改成功'}, status=status.HTTP_200_OK)


class ExpressTypeModelViewSet(ModelViewSet):

    queryset = ExpressType.objects.all()
    serializer_class = ExpressTypeModelSerializer

    def choices_expresstype(self, request):
        express_number = self.request.query_params.get('express_number', '')
        url = f'https://alayn.baidu.com/express/appdetail/get_com?num={express_number}'
        response = requests.get(url=url)
        try:
            result = response.json()
            express_type = ExpressType.objects.filter(express_type=result['data']['company']).first()
            if express_type:
                return Response(ExpressTypeModelSerializer(express_type).data, status=status.HTTP_200_OK)
            else:
                return Response({}, status=status.HTTP_200_OK)
        except:
            return Response({}, status=status.HTTP_200_OK)

    def syn_express(self, request):
        express_id = self.request.query_params.get('express_id', None)
        try:
            express = Express.objects.get(id=express_id)
        except:
            return Response({'error': '无效物流id'}, status=status.HTTP_400_BAD_REQUEST)
        if express.deliverystatus not in [0, 1, 5, 2, 6]:
            return Response({'error': '操作成功'}, status=status.HTTP_200_OK)
        try:
            order = ExpressOrderRelevance.objects.filter(express_id=express.id).first()
            phone = OrderAddress.objects.filter(order_id=order.order_id).first()
            if phone:
                result = KuaiDi100().track(express.express_type.express_type, express.express_number, phone.mobile[-4:])
            else:
                result = KuaiDi100().track(express.express_type.express_type, express.express_number)
            if result['message'] != 'ok':
                return Response({'error': result['message']}, status=status.HTTP_400_BAD_REQUEST)
            if result['status'] == '200':
                express.deliverystatus = result['state']
                express.save()
                for i in result['data']:
                    plan_data = {}
                    plan_data['express_id'] = express.id
                    plan_data['create_time'] = i['time']
                    plan_data['remark'] = i['context']
                    ExpressPlan.objects.get_or_create(defaults=plan_data, create_time=i['time'], express_id=express.id)
                if str(express.deliverystatus) == "3":
                    try:
                        sign_time = ExpressPlan.objects.filter(express_id=express.id).aggregate(Max('create_time'))['create_time__max']
                        express.sign_time = sign_time
                        express.save()
                    except:
                        pass
            else:
                express.deliverystatus = 9
                express.save()
                ExpressPlan.objects.create(remark=result['message'], create_time=datetime.datetime.now(), express_id=express.id)
        except Exception as e:
            print(e)
            return Response({'error': '操作失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class OrderAdvanceModelViewSet(ModelViewSet):

    queryset = OrderAdvance.objects.all().order_by('-id')
    serializer_class = OrderAdvanceModelSerializer
    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = OrderAdvanceFilter


class ExpressQuestModelViewSet(ModelViewSet):

    pagination_class = MyPage
    # filter_class = UpdateStockUsableMerchantFilter
    # filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('customer_user__mobile', )
    queryset = ExpressQuest.objects.all().order_by('-id')

    def get_serializer_class(self):
        return ExpressQuestListModelSerializer


class AliDiscountCouponGoodsAvailableModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = AliDiscountCouponGoodsAvailableFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('goods__name', )
    queryset = AliDiscountCouponGoodsAvailable.objects.all().order_by('-goods__sort')

    def get_serializer_class(self):
        return AliDiscountCouponGoodsAvailableListModelSerializer


class CustomizeActivityFlipCARDSModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('user__mobile', 'customeruservice__mobile')
    filter_class = CustomizeActivityFlipCARDSFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CustomizeActivityFlipCARDS.objects.all()
        elif level.level == 1:
            queryset = CustomizeActivityFlipCARDS.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CustomizeActivityFlipCARDS.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CustomizeActivityFlipCARDS.objects.filter(level__district_id=level.id)
        else:
            queryset = CustomizeActivityFlipCARDS.objects.filter(level__branch_id=level.id)
        queryset = queryset.order_by('-id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return CustomizeActivityFlipCARDSListModelSerializer
        else:
            return CustomizeActivityFlipCARDSRetrieveModelSerializer

    def export_customizeactivityflipcards(self, request):
        user = self.request.iser
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        level_id = self.request.query_params.get('level', None)
        open_status = self.request.query_params.get('open_status', None)
        search = self.request.query_params.get('search', None)
        received_time_begin = self.request.query_params.get('received_time_begin', None)
        received_time_end = self.request.query_params.get('received_time_end', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE open_status IN (2,4,6)'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id}) AND open_status IN (2,4,6)'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id}) AND open_status IN (2,4,6)'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id}) AND open_status IN (2,4,6)'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id}) AND open_status IN (2,4,6)'
        if search:
            condition = condition + f' AND mobile LIKE "%{search}%"'
        if open_status:
            condition = condition + f' AND open_status IN ({open_status})'
        if customizeactivity_id:
            condition = condition + f' AND customizeactivity_id={customizeactivity_id}'
        if received_time_begin:
            condition = condition + f' AND received_time >= "{received_time_begin + " 00:00:00"}"'
        if received_time_end:
            condition = condition + f' AND received_time <= "{received_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT k.*,l.business_name,business_type,business_amount,business_account FROM (SELECT i.*,j.c_activity_name FROM (SELECT g.*,h.c_amount,mchid FROM (SELECT e.*,f.v_activity_name,v_floor_amount,v_amount FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.activity_name FROM (SELECT id,open_status,DATE_FORMAT(open_time,'%Y-%m-%d %H:%i:%S')AS open_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,level_id,win_type,alidiscountcoupon_id,cashactivitydata_id,cashactivity_id,mobile,customizeactivity_id FROM tb_customize_activity_flip_cards {condition})AS a LEFT JOIN (SELECT id,activity_name FROM tb_customize_activity)AS b ON a.customizeactivity_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,activity_name AS v_activity_name,floor_amount AS v_floor_amount,amount AS v_amount FROM tb_ali_discount_coupon)AS f ON e.alidiscountcoupon_id=f.id)AS g LEFT JOIN (SELECT id,amount AS c_amount,mchid FROM tb_cash_activity_data)AS h ON g.cashactivitydata_id=h.id)AS i LEFT JOIN (SELECT id,name AS c_activity_name FROM tb_cash_activity)AS j ON i.cashactivity_id=j.id)AS k LEFT JOIN (SELECT customizeactivityflipcards_id,business_name,business_type,business_amount,business_account FROM tb_customize_activity_business_form)AS l ON k.id=l.customizeactivityflipcards_id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT k.*,l.business_name,business_type,business_amount,business_account FROM (SELECT i.*,j.c_activity_name FROM (SELECT g.*,h.c_amount,mchid FROM (SELECT e.*,f.v_activity_name,v_floor_amount,v_amount FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.activity_name FROM (SELECT id,open_status,DATE_FORMAT(open_time,'%Y-%m-%d %H:%i:%S')AS open_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,level_id,win_type,alidiscountcoupon_id,cashactivitydata_id,cashactivity_id,mobile,customizeactivity_id FROM tb_customize_activity_flip_cards)AS a LEFT JOIN (SELECT id,activity_name FROM tb_customize_activity)AS b ON a.customizeactivity_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,activity_name AS v_activity_name,floor_amount AS v_floor_amount,amount AS v_amount FROM tb_ali_discount_coupon)AS f ON e.alidiscountcoupon_id=f.id)AS g LEFT JOIN (SELECT id,amount AS c_amount,mchid FROM tb_cash_activity_data)AS h ON g.cashactivitydata_id=h.id)AS i LEFT JOIN (SELECT id,name AS c_activity_name FROM tb_cash_activity)AS j ON i.cashactivity_id=j.id)AS k LEFT JOIN (SELECT customizeactivityflipcards_id,business_name,business_type,business_amount,business_account FROM tb_customize_activity_business_form)AS l ON k.id=l.customizeactivityflipcards_id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '手机号', '抽奖时间', '领奖时间', '奖品类型', '奖品状态', '奖品活动名称', '金额', '门槛', '市公司', '区公司', '网点', '业务姓名', '业务账号', '业务金额', '业务类型']
        cols = ['id', 'activity_name', 'mobile', 'open_time', 'received_time', 'win_type', 'open_status', 'v_activity_name', 'v_amount', 'v_floor_amount', 'city_name', 'district_name', 'branch_name', 'business_name', 'business_account', 'business_amount', 'business_type']
        response = StreamingHttpResponse(self.get_customizeactivityflipcards_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('定制活动抽奖明细.csv'))
        return response

    def get_customizeactivityflipcards_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        TRADE_TYPE_ENUM = {
            1: '支付宝',
            2: '微信',
            3: '云闪付'
        }
        IS_HIT_ENUM = {
            0: '未命中',
            1: '已命中'
        }
        WIN_TYPE_ENUM = {
            1: "支付宝代金券",
            2: "微信代金券",
            3: "微信鼓励金",
            4: "谢谢参与",
            5: "积分",
            6: "支付宝鼓励金",
        }
        OPEN_STATUS_ENUM = {
            1: "初始状态",
            2: "待领取",
            3: "谢谢参与",
            4: "已领取",
            5: "失效状态",
            6: "领取失败",
        }
        BUSINESS_TYPE_ENUM = {
            1: "定期",
            2: "活期",
            3: "长期期缴"
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'win_type':
                    tmp_str += '"%s",' % (WIN_TYPE_ENUM[row[col]])
                elif col == 'open_status':
                    tmp_str += '"%s",' % (OPEN_STATUS_ENUM[row[col]])
                elif col == 'business_type':
                    try:
                        tmp_str += '"%s",' % (BUSINESS_TYPE_ENUM[row[col]])
                    except:
                        tmp_str += '"%s",' % ('')
                elif col == 'v_activity_name':
                    if row['win_type'] in [1, 2]:
                        tmp_str += '"%s",' % (row['v_activity_name'])
                    else:
                        tmp_str += '"%s",' % (row['c_activity_name'])
                elif col == 'v_amount':
                    if row['win_type'] in [1, 2]:
                        tmp_str += '"%s",' % (row['v_amount'])
                    else:
                        tmp_str += '"%s",' % (row['c_amount'])
                # elif col == 'amount':
                #     if row['payment_state'] in [1, 2, 3]:
                #         tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                #     else:
                #         tmp_str += '"%s",' % ''
                # elif col in ['order_no', 'trade_no']:
                #     tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class BindCardOrderModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('customeruservice__mobile', )
    filter_class = BindCardOrderFilter

    def get_queryset(self):
        queryset = BindCardOrder.objects.filter().order_by('-id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return BindCardOrderListModelSerializer
        else:
            return BindCardOrderRetrieveModelSerializer

    def export_bindcardorder(self, request):
        received_time_begin = self.request.query_params.get('received_time_begin', None)
        received_time_end = self.request.query_params.get('received_time_end', None)
        order_status = self.request.query_params.get('order_status', None)
        prize_status = self.request.query_params.get('prize_status', None)
        bank_type = self.request.query_params.get('bank_type', None)
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        search = self.request.query_params.get('search', None)
        condition = 'WHERE id!=0'
        if search:
            condition = condition + f' AND customeruservice_id IN (SELECT id FROM tb_u_customer_user_vice WHERE mobile LIKE "%{search}%")'
        if received_time_begin:
            condition = condition + f' AND customizeactivityflipcards_id IN (SELECT id FROM tb_customize_activity_flip_cards WHERE received_time >= "{received_time_begin + " 00:00:00"}")'
        if received_time_end:
            condition = condition + f' AND customizeactivityflipcards_id IN (SELECT id FROM tb_customize_activity_flip_cards WHERE received_time <= "{received_time_end + " 23:59:59"}")'
        if order_status:
            condition = condition + f' AND order_status IN ({order_status})'
        if prize_status:
            condition = condition + f' AND prize_status IN ({prize_status})'
        if bank_type:
            condition = condition + f' AND bank_type_sign IN ({bank_type})'
        if customizeactivity_id:
            condition = condition + f' AND customizeactivity_id IN ({customizeactivity_id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT m.*,n.city_name,district_name,branch_name FROM (SELECT k.*,l.name,wyfMerchantID,level_id FROM (SELECT i.*,j.merchant_id FROM (SELECT g.*,h.activity_name FROM (SELECT e.*,f.received_time,alidiscountcoupon_id FROM (SELECT c.*,d.bank_name FROM (SELECT a.*,b.mobile,unionid FROM (SELECT id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,description,bank_type,order_status,prize_status,customeruservice_id,customizeactivityflipcards_id,business_qrcode FROM tb_o_bind_card_order {condition})AS a LEFT JOIN (SELECT id,mobile,unionid FROM tb_u_customer_user_vice)AS b ON a.customeruservice_id=b.id)AS c LEFT JOIN (SELECT bank_code,bank_name FROM tb_bank_type)AS d ON c.bank_type=d.bank_code)AS e LEFT JOIN (SELECT id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,alidiscountcoupon_id FROM tb_customize_activity_flip_cards)AS f ON e.customizeactivityflipcards_id=f.id)AS g LEFT JOIN (SELECT id,activity_name FROM tb_ali_discount_coupon)AS h ON g.alidiscountcoupon_id=h.id)AS i LEFT JOIN (SELECT merchant_id,sn FROM tb_merchant_business_qrcode)AS j ON i.business_qrcode=j.sn)AS k LEFT JOIN (SELECT id,name,wyfMerchantID,level_id FROM tb_merchant)AS l ON k.merchant_id=l.id)AS m LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS n ON m.level_id=n.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT m.*,n.city_name,district_name,branch_name FROM (SELECT k.*,l.name,wyfMerchantID,level_id FROM (SELECT i.*,j.merchant_id FROM (SELECT g.*,h.activity_name FROM (SELECT e.*,f.received_time,alidiscountcoupon_id FROM (SELECT c.*,d.bank_name FROM (SELECT a.*,b.mobile,unionid FROM (SELECT id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,description,bank_type,order_status,prize_status,customeruservice_id,customizeactivityflipcards_id,business_qrcode FROM tb_o_bind_card_order)AS a LEFT JOIN (SELECT id,mobile,unionid FROM tb_u_customer_user_vice)AS b ON a.customeruservice_id=b.id)AS c LEFT JOIN (SELECT bank_code,bank_name FROM tb_bank_type)AS d ON c.bank_type=d.bank_code)AS e LEFT JOIN (SELECT id,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,alidiscountcoupon_id FROM tb_customize_activity_flip_cards)AS f ON e.customizeactivityflipcards_id=f.id)AS g LEFT JOIN (SELECT id,activity_name FROM tb_ali_discount_coupon)AS h ON g.alidiscountcoupon_id=h.id)AS i LEFT JOIN (SELECT merchant_id,sn FROM tb_merchant_business_qrcode)AS j ON i.business_qrcode=j.sn)AS k LEFT JOIN (SELECT id,name,wyfMerchantID,level_id FROM tb_merchant)AS l ON k.merchant_id=l.id)AS m LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS n ON m.level_id=n.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', 'unionid', '活动描述', '活动参与时间', '支付方式', '支付状态', '领券状态', '领券情况', '手机号码', '领取时间', '参与活动来源', '微邮付商户号', '商户名称', '市公司', '区县', '网点']
        cols = ['id', 'unionid', 'description', 'create_time', 'bank_name', 'order_status', 'prize_status', 'activity_name', 'mobile', 'received_time', 'source', 'wyfMerchantID', 'name', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_bindcardorder_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('验卡有礼完成明细.csv'))
        return response

    def get_bindcardorder_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        ORDER_STATUS_ENUM = {
            1: '待支付',
            2: '已支付',
            3: '支付失败',
            4: '已退款',
            5: '退款失败',
        }
        PRIZE_STATUS_ENUM = {
            1: '初始状态',
            2: '待领取',
            3: '已领取',
            4: '领取失败',
            5: '已作废',
            6: '待抽奖',
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'order_status':
                    tmp_str += '"%s",' % (ORDER_STATUS_ENUM[row[col]])
                elif col == 'source':
                    if row['business_qrcode']:
                        tmp_str += '"%s",' % ('商圈码扫码')
                    else:
                        tmp_str += '"%s",' % ('其他渠道')
                elif col == 'prize_status':
                    tmp_str += '"%s",' % (PRIZE_STATUS_ENUM[row[col]])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class JoinCustomizeActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('customeruservice__mobile', )
    filter_class = JoinCustomizeActivityFilter

    def get_queryset(self):
        sort = self.request.query_params.get('sort', None)
        queryset = JoinCustomizeActivity.objects.filter().order_by('-id')
        if sort == '1':
            queryset = queryset.order_by('count')
        if sort == '2':
            queryset = queryset.order_by('-count')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return JoinCustomizeActivityListModelSerializer
        else:
            return JoinCustomizeActivityRetrieveModelSerializer

    def export_joincustomizeactivity(self, request):
        user = self.request.iser
        is_join = self.request.query_params.get('is_join', None)
        join_time_begin = self.request.query_params.get('join_time_begin', None)
        join_time_end = self.request.query_params.get('join_time_end', None)
        search = self.request.query_params.get('search', None)
        count = self.request.query_params.get('count', None)
        condition = 'WHERE id >1'
        if search:
            condition = condition + f' AND customeruservice_id IN (SELECT id FROM tb_u_customer_user_vice WHERE mobile LIKE "%{search}%")'
        if join_time_begin:
            condition = condition + f' AND join_time >= "{join_time_begin + " 00:00:00"}"'
        if join_time_end:
            condition = condition + f' AND join_time <= "{join_time_end + " 23:59:59"}"'
        if is_join:
            condition = condition + f' AND is_join IN ({is_join})'
        if count:
            condition = condition + f' AND count IN ({count})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.is_psbc_card,mobile,wyf_gzh_openid,unionid FROM (SELECT a.*,b.activity_name FROM (SELECT id,DATE_FORMAT(join_time,'%Y-%m-%d %H:%i:%S')AS join_time,customeruservice_id,customizeactivity_id,is_join,count FROM tb_customize_activity_join {condition})AS a LEFT JOIN (SELECT id,activity_name FROM tb_customize_activity)AS b ON a.customizeactivity_id=b.id)AS c LEFT JOIN (SELECT id,wyf_gzh_openid,is_psbc_card,mobile,unionid FROM tb_u_customer_user_vice)AS d ON c.customeruservice_id=d.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT c.*,d.is_psbc_card,mobile,wyf_gzh_openid,unionid FROM (SELECT a.*,b.activity_name FROM (SELECT id,DATE_FORMAT(join_time,'%Y-%m-%d %H:%i:%S')AS join_time,customeruservice_id,customizeactivity_id,is_join,count FROM tb_customize_activity_join)AS a LEFT JOIN (SELECT id,activity_name FROM tb_customize_activity)AS b ON a.customizeactivity_id=b.id)AS c LEFT JOIN (SELECT id,wyf_gzh_openid,is_psbc_card,mobile,unionid FROM tb_u_customer_user_vice)AS d ON c.customeruservice_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', 'unionid', '活动名称', '任务状态', '任务进度', '领取任务时间', '手机号码', '是否微邮付公众号粉丝', '是否绑定邮储卡']
        cols = ['id', 'unionid', 'activity_name', 'is_join', 'count', 'join_time', 'mobile', 'wyf_gzh_openid', 'is_psbc_card']
        response = StreamingHttpResponse(self.get_joincustomizeactivity_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('消费有礼完成明细.csv'))
        return response

    def get_joincustomizeactivity_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_JOIN_ENUM = {
            0: '未报名',
            1: '进行中',
            2: '已完成'
        }
        IS_PSBC_CARD_ENUM = {
            0: '否',
            1: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_join':
                    tmp_str += '"%s",' % (IS_JOIN_ENUM[row[col]])
                elif col == 'is_psbc_card':
                    tmp_str += '"%s",' % (IS_PSBC_CARD_ENUM[row[col]])
                elif col == 'count':
                    if row[col]:
                        tmp_str += '"%s",' % f'第{row[col]}笔'
                    else:
                        tmp_str += '"%s",' % ''
                elif col == 'wyf_gzh_openid':
                    if row[col]:
                        tmp_str += '"%s",' % '是'
                    else:
                        tmp_str += '"%s",' % '否'
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def retrieve(self, request, *args, **kwargs):
        joincustomizeactivity = self.get_object()
        serializer = self.get_serializer(joincustomizeactivity)
        data = serializer.data
        if joincustomizeactivity.is_join == 0:
            order_info = {}
            order_info['list'] = []
            order_info['counts'] = 0
            order_info['page'] = self.request.query_params.get('page', 1)
            order_info['pages'] = 1
            order_info['pagesize'] = 10
            data['order_info'] = order_info
        else:
            customizeactivity = joincustomizeactivity.customizeactivity
            customizeactivitypayrules = customizeactivity.customizeactivitypayrules
            join_time = joincustomizeactivity.join_time
            available_end_time = customizeactivity.available_end_time
            total_count = customizeactivitypayrules.total_count
            amount = customizeactivitypayrules.amount
            draw_counts = customizeactivitypayrules.draw_counts.split(',')
            limit_merchant_count_by_day = customizeactivitypayrules.limit_merchant_count_by_day
            if customizeactivitypayrules.is_limit_merchant == 0:
                queryset = get_point_record_db_model(joincustomizeactivity.customeruservice_id).objects.filter(
                    pay_time__gte=join_time,
                    pay_time__lte=available_end_time,
                    amount__gte=amount,
                    customeruservice_id=joincustomizeactivity.customeruservice_id,
                    level__prov_id=7330
                ).values('shop_id').annotate(order_id=Min('id'))[0:total_count]
                if joincustomizeactivity.is_join == 1:
                    if len(queryset) >= total_count:
                        joincustomizeactivity.is_join = 2
                    joincustomizeactivity.count = len(queryset)
                    joincustomizeactivity.save()
                order_id_dict = {}
                i = 1
                for order in queryset:
                    order_id_dict[order['order_id']] = i
                    i += 1
            else:
                merchant_id_list = CustomizeActivityPayRulesLimitMerchant.objects.filter(
                    customizeactivity_id=customizeactivity.id).values_list('merchant_id', flat=True)
                queryset = get_point_record_db_model(joincustomizeactivity.customeruservice_id).objects.filter(
                    pay_time__gte=join_time,
                    pay_time__lte=available_end_time,
                    amount__gte=amount,
                    customeruservice_id=joincustomizeactivity.customeruservice_id,
                    merchant_id__in=merchant_id_list
                )
                order_id_list = []
                check_date_order = {}
                check_order_num = 0
                for order in queryset:
                    if check_order_num >= total_count:
                        break
                    pay_time = f'{order.pay_time.strftime("%Y%m%d")}{order.merchant_id}'
                    if pay_time not in check_date_order:
                        check_date_order[pay_time] = 0
                    if check_date_order[pay_time] >= limit_merchant_count_by_day:
                        continue
                    order_id_list.append(order.id)
                    check_date_order[pay_time] = check_date_order[pay_time] + 1
                    check_order_num += 1
                if joincustomizeactivity.is_join == 1:
                    if len(order_id_list) >= total_count:
                        joincustomizeactivity.is_join = 2
                    joincustomizeactivity.count = len(order_id_list)
                    joincustomizeactivity.save()
                order_id_dict = {}
                i = 1
                for order in order_id_list:
                    order_id_dict[order] = i
                    i += 1
            queryset = get_point_record_db_model(joincustomizeactivity.customeruservice_id).objects.filter(
                pay_time__gte=join_time,
                pay_time__lte=available_end_time,
                customeruservice_id=joincustomizeactivity.customeruservice_id,
            ).order_by('-id')
            page = self.paginate_queryset(queryset)
            schedule_list = []
            for query in page:
                try:
                    order_data = {}
                    order_data['pay_time'] = query.pay_time.strftime("%Y-%m-%d %H:%M:%S")
                    order_data['amount'] = query.amount
                    order_data['id'] = query.id
                    if query.bank_type == 'PSBC_DEBIT':
                        order_data['bank_type_sign_str'] = '邮储借记卡'
                    elif query.bank_type == 'PSBC_CREDIT':
                        order_data['bank_type_sign_str'] = '邮储信用卡'
                        order_data['bank_type_sign'] = 2
                    else:
                        order_data['bank_type_sign_str'] = '其他'
                        order_data['bank_type_sign'] = 3
                    merchant = query.merchant
                    if merchant:
                        order_data['merchant_name'] = merchant.name
                        order_data['merchant_id'] = merchant.id
                    else:
                        order_data['merchant_name'] = ''
                        order_data['merchant_id'] = ''
                    if query.id in order_id_dict:
                        order_data['count'] = f'第{order_id_dict[query.id]}笔'
                        order_data['is_effective'] = 1
                        if str(order_id_dict[query.id]) in draw_counts:
                            if not query.customizeactivityflipcards:
                                customizeactivityrules = CustomizeActivityRules.objects.get(customizeactivity_id=customizeactivity.id, index=order_id_dict[query.id])
                                identify = f'{str(datetime.datetime.now()).replace("-", "").replace(":", "").replace(" ", "").replace(".", "")}{"%06d" % randint(0, 999999)}{"%07d" % joincustomizeactivity.customeruservice_id}'
                                instance_data = dict()
                                instance_data['identify'] = identify
                                instance_data['customeruservice_id'] = joincustomizeactivity.customeruservice_id
                                instance_data['mobile'] = joincustomizeactivity.customeruservice.mobile
                                instance_data['gears'] = 1
                                instance_data['customizeactivity_id'] = customizeactivity.id
                                if customizeactivityrules.prize_type in [1, 2]:
                                    instance_data['alidiscountcoupon_id'] = customizeactivityrules.alidiscountcoupon_id
                                    instance_data['win_type'] = customizeactivityrules.prize_type
                                elif customizeactivityrules.prize_type in [3, 6]:
                                    instance_data['cashactivity_id'] = customizeactivityrules.cashactivity_id
                                    instance_data['win_type'] = customizeactivityrules.prize_type
                                instance = CustomizeActivityFlipCARDS.objects.create(**instance_data)
                                if instance.win_type in [1, 2]:
                                    instance.open_status = 2
                                    instance.open_time = datetime.datetime.now()
                                    instance.save()
                                    alidiscountcoupon = instance.alidiscountcoupon
                                    if alidiscountcoupon.remain_number <= 0:
                                        x = list(customizeactivity.customizeactivityrules_set.filter(alidiscountcoupon__remain_number__gt=0).values_list('alidiscountcoupon_id', flat=True))
                                        if x:
                                            shuffle(x)
                                            alidiscountcoupon = AliDiscountCoupon.objects.get(id=x[0])
                                            instance.alidiscountcoupon_id = alidiscountcoupon.id
                                            instance.save()
                                        else:
                                            instance.open_status = 3
                                            instance.win_type = 4
                                            instance.open_time = datetime.datetime.now()
                                            instance.save()
                                    activity_id = alidiscountcoupon.id
                                    while True:
                                        alidiscountcoupon = AliDiscountCoupon.objects.get(id=activity_id)
                                        activity_origin_send_count = alidiscountcoupon.send_count
                                        activity_origin_remain_number = alidiscountcoupon.remain_number
                                        activity_new_send_count = activity_origin_send_count + 1
                                        activity_new_remain_number = activity_origin_remain_number - 1
                                        if activity_new_remain_number < 0:
                                            instance.open_status = 3
                                            instance.win_type = 4
                                            instance.open_time = datetime.datetime.now()
                                            instance.save()
                                        result = AliDiscountCoupon.objects.filter(id=activity_id,send_count=activity_origin_send_count).update(send_count=activity_new_send_count, remain_number=activity_new_remain_number)
                                        if result == 0:
                                            continue
                                        break
                                    alidiscountcouponinfo_data = {}
                                    alidiscountcouponinfo_data['alidiscountcoupon_id'] = alidiscountcoupon.id
                                    alidiscountcouponinfo_data['level_id'] = instance.level_id
                                    alidiscountcouponinfo_data['status'] = 0
                                    alidiscountcouponinfo_data['template_id'] = alidiscountcoupon.template_id
                                    out_biz_no = f'{str(datetime.datetime.now()).replace("-", "").replace(":", "").replace(" ", "").replace(".", "")}{"%06d" % randint(0, 999999)}{"%07d" % joincustomizeactivity.customeruservice_id}'
                                    alidiscountcouponinfo_data['out_biz_no'] = out_biz_no
                                    alidiscountcouponinfo_data['gmt_distributed'] = datetime.datetime.now()
                                    alidiscountcouponinfo, crete = AliDiscountCouponInfo.objects.get_or_create(defaults=alidiscountcouponinfo_data, out_biz_no=out_biz_no)
                                    instance.alidiscountcouponinfo_id = alidiscountcouponinfo.id
                                    instance.save()
                                else:
                                    instance.open_status = 2
                                    instance.open_time = datetime.datetime.now()
                                    instance.save()
                                    cashactivity = instance.cashactivity
                                    cashactivityrule = CashActivityRule.objects.filter(cash_activity_id=cashactivity.id, is_delete=0).first()
                                    if cashactivityrule.amount > cashactivity.remain_amount:
                                        instance.open_status = 3
                                        instance.win_type = 4
                                        instance.open_time = datetime.datetime.now()
                                        instance.save()
                                    amount = cashactivityrule.amount
                                    activity_id = cashactivity.id
                                    while True:
                                        activity = CashActivity.objects.get(id=activity_id)
                                        activity_origin_take_amount = activity.take_amount
                                        activity_origin_remain_amount = activity.remain_amount
                                        if activity_origin_remain_amount < amount:
                                            instance.open_status = 3
                                            instance.win_type = 4
                                            instance.open_time = datetime.datetime.now()
                                            instance.save()
                                        activity_new_take_amount = activity_origin_take_amount + amount
                                        activity_new_remain_amount = activity_origin_remain_amount - amount
                                        result = CashActivity.objects.filter(take_amount=activity_origin_take_amount, remain_amount=activity_origin_remain_amount, id=activity_id).update(take_amount=activity_new_take_amount, remain_amount=activity_new_remain_amount)
                                        if result == 0:
                                            continue
                                        break
                                    date_now = datetime.datetime.now()
                                    activity_data = {}
                                    activity_data['amount'] = amount
                                    activity_data['create_time'] = date_now
                                    activity_data['cash_activity_id'] = activity.id
                                    activity_data['level_id'] = instance.level_id
                                    activity_data['state'] = 1
                                    identify = random_str()
                                    nonce_str = random_str()
                                    activity_data['identify'] = identify
                                    activity_data['nonce_str'] = nonce_str
                                    activity_data['desc'] = activity.name
                                    activity_data['partner_trade_no'] = f'{date_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}{"%07d" % joincustomizeactivity.customeruservice_id}'
                                    activity_data['expired_time'] = date_now + datetime.timedelta(hours=11)
                                    activity_dat, create = CashActivityData.objects.get_or_create(defaults=activity_data, identify=identify)
                                    instance.cashactivitydata_id = activity_dat.id
                                    instance.amount = amount
                                    instance.save()
                                query.customizeactivityflipcards_id = instance.id
                                query.save()
                            else:
                                instance = query.customizeactivityflipcards
                            if instance.win_type in [1, 2]:
                                order_data['open_status'] = instance.open_status
                                order_data['open_status_str'] = instance.get_open_status_display()
                                order_data['activity_name'] = instance.alidiscountcoupon.activity_name
                            else:
                                order_data['open_status'] = instance.open_status
                                order_data['open_status_str'] = instance.get_open_status_display()
                                order_data['activity_name'] = instance.cashactivity.name
                        else:
                            order_data['open_status'] = ''
                            order_data['open_status_str'] = ''
                            order_data['activity_name'] = ''
                    else:
                        order_data['count'] = ''
                        order_data['is_effective'] = 0
                        order_data['open_status'] = ''
                        order_data['open_status_str'] = ''
                        order_data['activity_name'] = ''
                    schedule_list.append(order_data)
                except Exception as e:
                    pass
            order_info = {}
            order_info['list'] = schedule_list
            order_info['counts'] = len(queryset)
            order_info['page'] = self.request.query_params.get('page', 1)
            order_info['pages'] = math.ceil(len(queryset)/10)
            order_info['pagesize'] = 10
            data['order_info'] = order_info
        return Response(data, status=status.HTTP_200_OK)


class CustomerUserViceModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('mobile', 'unionid')
    filter_class = CustomerUserViceFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        sort = self.request.query_params.get('sort', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CustomerUserVice.objects.all()
        elif level.level == 1:
            queryset = CustomerUserVice.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CustomerUserVice.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CustomerUserVice.objects.filter(level__district_id=level.id)
        else:
            queryset = CustomerUserVice.objects.filter(level__branch_id=level.id)
        if sort == '1':
            queryset = queryset.order_by('point')
        if sort == '2':
            queryset = queryset.order_by('-point')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return CustomerUserViceListModelSerializer
        else:
            return CustomerUserViceRetrieveModelSerializer

    def customeruservice_order(self, request):
        try:
            level_id = self.request.query_params.get('level_id', None)
            customeruservice_id = self.request.query_params.get('customeruservice_id', None)
            bank_type = self.request.query_params.get('bank_type', None)
            pay_time_begin = self.request.query_params.get('pay_time_begin', None)
            pay_time_end = self.request.query_params.get('pay_time_end', None)
            amount_min = self.request.query_params.get('amount_min', None)
            amount_max = self.request.query_params.get('amount_max', None)
            search = self.request.query_params.get('search', None)
            page = int(self.request.query_params.get('page', 1))
            pagesize = int(self.request.query_params.get('pagesize', 10))
            if not customeruservice_id:
                return Response({'error': '请上传所属用户id'}, status=status.HTTP_400_BAD_REQUEST)
            try:
                customeruservice = CustomerUserVice.objects.get(id=customeruservice_id)
            except:
                return Response({'error': '无效用户id'}, status=status.HTTP_400_BAD_REQUEST)
            condition = f'WHERE customeruservice_id={customeruservice_id}'
            if level_id:
                level = Level.objects.filter(id=level_id).first()
                level_list = get_level_list(level)
                level_tuple = tuple(level_list)
                if len(level_tuple) == 1:
                    level_tuple = str(level_tuple).replace(',', '')
                condition = condition + f' AND level_id IN {level_tuple}'
            if pay_time_begin:
                condition = condition + f' AND pay_time >= "{pay_time_begin + " 00:00:00"}"'
            if pay_time_end:
                condition = condition + f' AND pay_time <= "{pay_time_end + " 23:59:59"}"'
            if amount_min:
                condition = condition + f' AND money>={amount_min}'
            if amount_max:
                condition = condition + f' AND money<={amount_max}'
            if bank_type:
                condition = condition + f' AND bank_type_id IN ({bank_type})'
            if search:
                merchant_list = Merchant.objects.filter(name__contains=search).values_list('id', flat=True)
                if merchant_list:
                    merchant_tuple = tuple(merchant_list)
                    if len(merchant_tuple) == 1:
                        merchant_tuple = str(merchant_tuple).replace(',', '')
                    condition = condition + f' AND merchant_id IN {merchant_tuple}'
            conn = pymysql.connect(host=settings.DATABASES['business_custmoer_order_data']['HOST'],
                                   port=settings.DATABASES['business_custmoer_order_data']['PORT'],
                                   database=settings.DATABASES['business_custmoer_order_data']['NAME'],
                                   user=settings.DATABASES['business_custmoer_order_data']['USER'],
                                   password=settings.DATABASES['business_custmoer_order_data']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            sql = f'''
            SELECT COUNT(id) AS counts FROM tb_o_point_order_info_{customeruservice.id%900} {condition};
            '''
            cursor.execute(sql)
            row = cursor.fetchone()
            counts = row['counts']
            pages = math.ceil(counts/pagesize)
            if pages == 0:
                pages = 1
            if page > pages:
                return Response({'error': '无效页面'}, status=status.HTTP_400_BAD_REQUEST)
            condition = condition + f' ORDER BY -id LIMIT {(page-1)*pagesize},{pagesize}'
            sql = f'''
            SELECT id,DATE_FORMAT(pay_time,'%Y-%m-%d %H:%i:%S') AS pay_time,bank_type_id,merchant_id,level_id,money FROM tb_o_point_order_info_{customeruservice.id%900} {condition};
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            lists = []
            for row in rows:
                if row['bank_type_id'] == 1:
                    row['bank_type'] = '邮储信用卡'
                elif row['bank_type_id'] == 2:
                    row['bank_type'] = '邮储借记卡'
                else:
                    row['bank_type'] = '其他'
                if row['merchant_id']:
                    merchant = Merchant.objects.get(id=row['merchant_id'])
                    row['merchant_name'] = merchant.short_name
                    row['level_name'] = merchant.level.name
                else:
                    row['merchant_name'] = ''
                    row['level_name'] = ''
                lists.append(row)
            data = {}
            data['counts'] = counts
            data['lists'] = lists
            data['page'] = page
            data['pages'] = pages
            data['pagesize'] = pagesize
            return Response(data, status=status.HTTP_200_OK)
        except:
            return Response({'error': '系统繁忙'}, status=status.HTTP_400_BAD_REQUEST)

    def customeruservice_order_map(self, request):
        try:
            level_id = self.request.query_params.get('level_id', None)
            customeruservice_id = self.request.query_params.get('customeruservice_id', None)
            bank_type = self.request.query_params.get('bank_type', None)
            pay_time_begin = self.request.query_params.get('pay_time_begin', None)
            pay_time_end = self.request.query_params.get('pay_time_end', None)
            amount_min = self.request.query_params.get('amount_min', None)
            amount_max = self.request.query_params.get('amount_max', None)
            search = self.request.query_params.get('search', None)
            longitude = self.request.query_params.get('longitude', None)
            latitude = self.request.query_params.get('latitude', None)
            max_distance = self.request.query_params.get('max_distance', 100)
            if not max_distance:
                return Response({'error': '请上传比例尺'}, status=status.HTTP_400_BAD_REQUEST)
            if not all([longitude, latitude]):
                return Response({'error': '请上传经纬度'}, status=status.HTTP_400_BAD_REQUEST)
            if int(Decimal(max_distance)) > 5000:
                return Response({'error': '比例尺最大范围为5公里'}, status=status.HTTP_400_BAD_REQUEST)
            if not customeruservice_id:
                return Response({'error': '请上传所属用户id'}, status=status.HTTP_400_BAD_REQUEST)
            try:
                customeruservice = CustomerUserVice.objects.get(id=customeruservice_id)
            except:
                return Response({'error': '无效用户id'}, status=status.HTTP_400_BAD_REQUEST)
            condition = f'WHERE customeruservice_id={customeruservice_id}'
            if level_id:
                level = Level.objects.filter(id=level_id).first()
                level_list = get_level_list(level)
                level_tuple = tuple(level_list)
                if len(level_tuple) == 1:
                    level_tuple = str(level_tuple).replace(',', '')
                condition = condition + f' AND level_id IN {level_tuple}'
            if pay_time_begin:
                condition = condition + f' AND pay_time >= "{pay_time_begin + " 00:00:00"}"'
            if pay_time_end:
                condition = condition + f' AND pay_time <= "{pay_time_end + " 23:59:59"}"'
            if amount_min:
                condition = condition + f' AND money>={amount_min}'
            if amount_max:
                condition = condition + f' AND money<={amount_max}'
            if bank_type:
                condition = condition + f' AND bank_type_id IN ({bank_type})'
            if search:
                merchant_list = Merchant.objects.filter(name__contains=search).values_list('id', flat=True)
                if merchant_list:
                    merchant_tuple = tuple(merchant_list)
                    if len(merchant_tuple) == 1:
                        merchant_tuple = str(merchant_tuple).replace(',', '')
                    condition = condition + f' AND merchant_id IN {merchant_tuple}'
            conn = pymysql.connect(host=settings.DATABASES['business_custmoer_order_data']['HOST'],
                                   port=settings.DATABASES['business_custmoer_order_data']['PORT'],
                                   database=settings.DATABASES['business_custmoer_order_data']['NAME'],
                                   user=settings.DATABASES['business_custmoer_order_data']['USER'],
                                   password=settings.DATABASES['business_custmoer_order_data']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            sql = f'''
            SELECT id,DATE_FORMAT(pay_time,'%Y-%m-%d %H:%i:%S') AS pay_time,bank_type_id,merchant_id,level_id,money FROM tb_o_point_order_info_{customeruservice.id%900} {condition};
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            merchant_id_list = []
            for row in rows:
                if row['merchant_id']:
                    merchant_id_list.append(row['merchant_id'])
            merchant_dict = {}
            if merchant_id_list:
                gcd_formula = "6371 * acos(least(greatest(cos(radians(%s)) * cos(radians(latitude)) * cos(radians(longitude) - radians(%s)) + sin(radians(%s)) * sin(radians(latitude)) , -1), 1))"
                sql = RawSQL(gcd_formula, (latitude, longitude, latitude))
                queryset = Merchant.objects.filter(id__in=merchant_id_list).annotate(distance=sql).filter(distance__lte=float(max_distance)/100).order_by(F('distance').asc(nulls_last=True))
                for merchant in queryset:
                    merchant_data = {}
                    merchant_data['id'] = merchant.id
                    merchant_data['order_info'] = []
                    merchant_data['latitude'] = merchant.latitude
                    merchant_data['longitude'] = merchant.longitude
                    merchant_data['merchant_name'] = merchant.name
                    merchant_data['count'] = 0
                    merchant_data['amount'] = Decimal('0')
                    distance = geodesic((merchant.latitude, merchant.longitude), (latitude, longitude)).m
                    if distance > 1000:
                        distance = f'{round(distance / 1000, 1)} km'
                    else:
                        distance = f'{int(distance)} m'
                    merchant_data['distance'] = distance
                    merchant_dict[merchant.id] = merchant_data
            lists = []
            if merchant_dict:
                for row in rows:
                    if row['bank_type_id'] == 1:
                        row['bank_type'] = '邮储信用卡'
                    elif row['bank_type_id'] == 2:
                        row['bank_type'] = '邮储借记卡'
                    else:
                        row['bank_type'] = '其他'
                    if row['merchant_id']:
                        merchant = Merchant.objects.get(id=row['merchant_id'])
                        row['merchant_name'] = merchant.short_name
                        row['level_name'] = merchant.level.name
                    else:
                        row['merchant_name'] = ''
                        row['level_name'] = ''
                    if row['merchant_id'] in merchant_dict:
                        merchant = merchant_dict[row['merchant_id']]
                        merchant['order_info'].append(row)
                        merchant['count'] += 1
                        merchant['amount'] += Decimal(row['money'])
                for key, value in merchant_dict.items():
                    lists.append(value)
            return Response({'data': lists}, status=status.HTTP_200_OK)
        except:
            return Response({'error': '系统繁忙'}, status=status.HTTP_400_BAD_REQUEST)


class MerchantEightEightDrawModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('business_no', 'merchantuser__mobile')
    filter_class = MerchantEightEightDrawFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantEightEightDraw.objects.all()
        elif level.level == 1:
            queryset = MerchantEightEightDraw.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantEightEightDraw.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantEightEightDraw.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantEightEightDraw.objects.filter(level__branch_id=level.id)
        return queryset.filter(id__gt=16).order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantEightEightDrawListModelSerializer
        else:
            return MerchantEightEightDrawRetrieveModelSerializer

    def export_merchanteightdraw(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        amount_min = self.request.query_params.get('amount_min', None)
        amount_max = self.request.query_params.get('amount_max', None)
        state = self.request.query_params.get('state', None)
        activity_id = self.request.query_params.get('activity_id', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE win_type!=6 AND id>16'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id}) AND win_type!=6 AND id>16'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id}) AND win_type!=6 AND id>16'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id}) AND win_type!=6 AND id>16'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id}) AND win_type!=6 AND id>16'
        if search:
            condition = condition + f' AND (merchantuser_id IN (SELECT id FROM tb_m_merchant_user WHERE mobile LIKE "%{search}%") OR business_no LIKE "%{search}%")'
        if state:
            condition = condition + f' AND state in ({state})'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if amount_min:
            condition = condition + f' AND amount >= {amount_min}'
        if amount_max:
            condition = condition + f' AND amount <= {amount_max}'
        if activity_id:
            condition = condition + f' AND activity_id = {activity_id}'

        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT i.*,j.channel_name FROM (SELECT g.*,h.activity_name FROM (SELECT * FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.mobile FROM (SELECT id,business_no,amount,payment_no,state,merchantuser_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,login_id,realname,return_msg,level_id,activity_id,channel_id FROM tb_merchant_eigth_eight_draw {condition})AS a LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS b ON a.merchantuser_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT wyfMerchantID,short_name FROM tb_merchant)AS f ON e.business_no=f.wyfMerchantID)AS g LEFT JOIN (SELECT activity_id,activity_name FROM tb_merchant_eight_eight_activity)AS h ON g.activity_id=h.activity_id)AS i LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS j ON i.channel_id=j.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT i.*,j.channel_name FROM (SELECT g.*,h.activity_name FROM (SELECT * FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.mobile FROM (SELECT id,business_no,amount,payment_no,state,merchantuser_id,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,login_id,realname,return_msg,level_id,activity_id,channel_id FROM tb_merchant_eigth_eight_draw)AS a LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS b ON a.merchantuser_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT wyfMerchantID,short_name FROM tb_merchant)AS f ON e.business_no=f.wyfMerchantID)AS g LEFT JOIN (SELECT activity_id,activity_name FROM tb_merchant_eight_eight_activity)AS h ON g.activity_id=h.activity_id)AS i LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS j ON i.channel_id=j.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '商户号', '商户简称', '付款单号', '状态', '中奖金额', '抽奖时间', '领奖时间', '领奖账号', '实名姓名', '领奖人手机号', '市公司', '区公司', '网点', '派发平台', '领取失败原因']
        cols = ['id', 'activity_name', 'business_no', 'short_name', 'payment_no', 'state', 'amount', 'create_time', 'received_time', 'login_id', 'realname', 'mobile', 'city_name', 'district_name', 'branch_name', 'channel_name', 'return_msg']
        response = StreamingHttpResponse(self.get_merchanteightdraw_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('88节达标商户中奖明细.csv'))
        return response

    def get_merchanteightdraw_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATE_ENUM = {
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            5: '领取中'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'state':
                    tmp_str += '"%s",' % (STATE_ENUM[row[col]])
                elif col == 'return_msg':
                    if row['state'] == 3:
                        tmp_str += '"%s",' % (row[col])
                elif col in ['payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBusinessQRCodeModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('merchant__wyfMerchantID', 'sn')
    filter_class = MerchantBusinessQRCodeFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBusinessQRCode.objects.all()
        elif level.level == 1:
            queryset = MerchantBusinessQRCode.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBusinessQRCode.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBusinessQRCode.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBusinessQRCode.objects.filter(level__branch_id=level.id)
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantBusinessQRCodeListModelSerializer
        else:
            return MerchantBusinessQRCodeRetrieveModelSerializer

    def export_merchantbusinessqrcode(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        is_bind = self.request.query_params.get('is_bind', None)
        is_c_bind = self.request.query_params.get('is_c_bind', None)
        search = self.request.query_params.get('search', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id!=0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (sn LIKE "%{search}%" OR merchant_id IN (SELECT id FROM tb_merchant WHERE wyfMerchantID LIKE "%{search}%"))'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        if is_bind:
            condition = condition + f' AND is_bind IN ({is_bind})'
        if is_c_bind:
            condition = condition + f' AND is_c_bind IN ({is_c_bind})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT k.*,l.mobile FROM (SELECT i.*,j.state,received_time,amount FROM (SELECT g.*,h.visit_count FROM (SELECT e.*,f.nickname FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,short_name,wyfMerchantID FROM (SELECT id,sn,merchant_id,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S') AS bind_time,is_bind,customeruservice_id,is_c_bind,DATE_FORMAT(c_bind_time,'%Y-%m-%d %H:%i:%S') AS c_bind_time,level_id,user_id,inviter_count,inviter_total_count,DATE_FORMAT(award_time,'%Y-%m-%d %H:%i:%S') AS award_time,is_achieve,cashactivitydata_id FROM tb_merchant_business_qrcode {condition})AS a LEFT JOIN (SELECT id,name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,nickname FROM tb_u_user)AS f ON e.user_id=f.id)AS g LEFT JOIN (SELECT qrcode_id,COUNT(qrcode_id) AS visit_count FROM tb_merchant_business_qrcode_visit GROUP BY qrcode_id)AS h ON g.id=h.qrcode_id)AS i LEFT JOIN (SELECT id,state,received_time,amount FROM tb_cash_activity_data)AS j ON i.cashactivitydata_id=j.id)AS k LEFT JOIN (SELECT id,mobile FROM tb_u_customer_user_vice)AS l ON k.customeruservice_id=l.id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT k.*,l.mobile FROM (SELECT i.*,j.state,received_time,amount FROM (SELECT g.*,h.visit_count FROM (SELECT e.*,f.nickname FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.name,short_name,wyfMerchantID FROM (SELECT id,sn,merchant_id,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S') AS bind_time,is_bind,customeruservice_id,is_c_bind,DATE_FORMAT(c_bind_time,'%Y-%m-%d %H:%i:%S') AS c_bind_time,level_id,user_id,inviter_count,inviter_total_count,DATE_FORMAT(award_time,'%Y-%m-%d %H:%i:%S') AS award_time,is_achieve,cashactivitydata_id FROM tb_merchant_business_qrcode)AS a LEFT JOIN (SELECT id,name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,nickname FROM tb_u_user)AS f ON e.user_id=f.id)AS g LEFT JOIN (SELECT qrcode_id,COUNT(qrcode_id) AS visit_count FROM tb_merchant_business_qrcode_visit GROUP BY qrcode_id)AS h ON g.id=h.qrcode_id)AS i LEFT JOIN (SELECT id,state,received_time,amount FROM tb_cash_activity_data)AS j ON i.cashactivitydata_id=j.id)AS k LEFT JOIN (SELECT id,mobile FROM tb_u_customer_user_vice)AS l ON k.customeruservice_id=l.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '商圈编码', '码牌绑定状态', '绑定时间', '微邮付商户号', '商户全称', '商户简称', '市公司', '区县', '网点', '客户经理姓名', '店主认证状态', '认证时间', '店主手机号', '扫码人数', '邀请数', '邀请成功数', '邀约任务完成状态', '客户经理领取奖励状态', '领奖时间', '领取奖励金额']
        cols = ['id', 'sn', 'is_bind', 'bind_time', 'wyfMerchantID', 'name', 'short_name', 'city_name', 'district_name', 'branch_name', 'nickname', 'is_c_bind', 'c_bind_time', 'mobile', 'visit_count', 'inviter_total_count', 'inviter_count', 'is_achieve', 'state', 'received_time', 'amount']
        response = StreamingHttpResponse(self.get_merchantbusinessqrcode_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商圈码绑定状态.csv'))
        return response

    def get_merchantbusinessqrcode_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_BIND_ENUM = {
            0: '未绑定',
            1: '已绑定'
        }
        IS_C_BIND_ENUM = {
            0: '未认证',
            1: '已认证'
        }
        IS_ACHIEVE_ENUM = {
            0: '未完成',
            1: '已完成'
        }
        STATE_ENUM = {
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            4: '已撤销',
            5: '领取中'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_bind':
                    tmp_str += '"%s",' % (IS_BIND_ENUM[row[col]])
                elif col == 'is_c_bind':
                    tmp_str += '"%s",' % (IS_C_BIND_ENUM[row[col]])
                elif col == 'is_achieve':
                    tmp_str += '"%s",' % (IS_ACHIEVE_ENUM[row[col]])
                elif col == 'state':
                    try:
                        tmp_str += '"%s",' % (STATE_ENUM[row[col]])
                    except:
                        tmp_str += '"%s",' % ('待领取')
                elif col in ['inviter_total_count', 'inviter_count', 'visit_count', 'amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBusinessQRCodeVisitModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__wyfMerchantID', 'sn')
    filter_class = MerchantBusinessQRCodeVisitFilter

    def get_queryset(self):
        queryset = MerchantBusinessQRCodeVisit.objects.all().order_by('-create_time')
        return queryset

    def get_serializer_class(self):
        return MerchantBusinessQRCodeVisitListModelSerializer


class CustomerUserViceInviterRecordModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__wyfMerchantID', 'sn')
    filter_class = CustomerUserViceInviterRecordFilter

    def get_queryset(self):
        queryset = CustomerUserViceInviterRecord.objects.all().order_by('-create_time')
        return queryset

    def get_serializer_class(self):
        return CustomerUserViceInviterRecordListModelSerializer


class OrderInfoModelViewSet(ModelViewSet):
    '''订单'''

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__name', 'ordergoods__goods__name', 'customer_user__mobile')
    filter_class = OrderInfoFilter

    def get_serializer_class(self):
        if self.action == 'list':
            return OrderInfoListModelSerializer
        else:
            return OrderInfoRetrieveModelSerializer

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        search = self.request.query_params.get('search', None)
        customeruser_mobile = self.request.query_params.get('customeruser_mobile', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        payment_time_end = self.request.query_params.get('payment_time_end', None)
        use_time_end = self.request.query_params.get('use_time_end', None)
        try:
            if '_mutable' in self.request.query_params.__dict__:
                self.request.query_params.__dict__['_mutable'] = True
        except:
            pass
        if create_time_end:
            self.request.query_params['create_time_end'] = f'{create_time_end} 23:59:59'
        if payment_time_end:
            self.request.query_params['payment_time_end'] = f'{payment_time_end} 23:59:59'
        if use_time_end:
            self.request.query_params['use_time_end'] = f'{use_time_end} 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = OrderInfo.objects.all()
        elif level.level == 1:
            queryset = OrderInfo.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = OrderInfo.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = OrderInfo.objects.filter(level__district_id=level.id)
        else:
            queryset = OrderInfo.objects.filter(level__branch_id=level.id)
        if customeruser_mobile:
            customeruservice_id = CustomerUserVice.objects.filter(mobile=customeruser_mobile).values_list('id', flat=True)
            if customeruservice_id:
                queryset = queryset.filter(customeruservice_id__in=list(customeruservice_id))
            else:
                queryset = queryset.filter(id=1)
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": [{"_id": "asc"}],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            body['query']['bool']['must_not'].append({"wildcard": {"text": "*vm*"}})
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # print(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            queryset = queryset.filter(id__in=id_list)
        return queryset.order_by('-create_time')

    def export_orderinfo(self, request):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        payment_time_begin = self.request.query_params.get('payment_time_begin', None)
        payment_time_end = self.request.query_params.get('payment_time_end', None)
        use_time_begin = self.request.query_params.get('use_time_begin', None)
        use_time_end = self.request.query_params.get('use_time_end', None)
        total_price_min = self.request.query_params.get('total_price_min', None)
        total_price_max = self.request.query_params.get('total_price_max', None)
        merchant_id = self.request.query_params.get('merchant_id', None)
        customeruser_id = self.request.query_params.get('customeruser_id', None)
        state = self.request.query_params.get('state', None)
        search = self.request.query_params.get('search', None)
        order_species = self.request.query_params.get('order_species', None)
        goods_id = self.request.query_params.get('goods_id', None)
        customeruser_mobile = self.request.query_params.get('customeruser_mobile', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id!=1'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": [],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            body['query']['bool']['must_not'].append({"wildcard": {"text": "*vm*"}})
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # return Response(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            if id_list:
                if len(id_list) == 1:
                    condition = condition + f' AND id IN {tuple(id_list)}'.replace(',', '')
                else:
                    condition = condition + f' AND id IN {tuple(id_list)}'
            # condition = condition + f' AND (id in (SELECT order_id FROM tb_order_goods WHERE merchant_id IN (SELECT id FROM tb_merchant WHERE name LIKE "%{search}%") OR goods_id IN (SELECT id FROM tb_goods WHERE name LIKE "%{search}%") OR customer_user_id IN (SELECT id FROM tb_u_customer_user WHERE mobile LIKE "%{search}%")))'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if payment_time_begin:
            condition = condition + f' AND payment_time >= "{payment_time_begin + " 00:00:00"}"'
        if payment_time_end:
            condition = condition + f' AND payment_time <= "{payment_time_end + " 23:59:59"}"'
        if use_time_begin:
            condition = condition + f' AND use_time >= "{use_time_begin + " 00:00:00"}"'
        if use_time_end:
            condition = condition + f' AND use_time <= "{use_time_end + " 23:59:59"}"'
        if total_price_min:
            condition = condition + f' AND total_price >= "{total_price_min}"'
        if total_price_max:
            condition = condition + f' AND total_price <= "{total_price_max}"'
        if state:
            condition = condition + f' AND state in ({state})'
        if order_species:
            condition = condition + f' AND order_species in ({order_species})'
        if merchant_id:
            condition = condition + f' AND merchant_id={merchant_id}'
        if customeruser_id:
            condition = condition + f' AND customer_user_id={customeruser_id}'
        if goods_id:
            condition = condition + f' AND id IN (SELECT order_id FROM tb_order_goods WHERE goods_id={goods_id})'
        if customeruser_mobile:
            condition = condition + f' AND customeruservice_id IN (SELECT id FROM tb_u_customer_user_vice WHERE mobile="{customeruser_mobile}")'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT t.*,u.address_mobile,receiver,province,city,district,place FROM (SELECT r.*,s.vice_mobile,vice_realname FROM (SELECT p.*,q.merhant_acount,merchant_city_name,merhant_district_name,merchant_branch_name,merchant_name,wyfMerchantID FROM (SELECT l.*,m.goods_name,goods_count,goods_price FROM (SELECT g.*,h.coupons_id,coupons_status,coupons_use_time FROM (SELECT c.*,d.customer_level,mobile,realname FROM (SELECT a.*, b.order_city_name,order_district_name,order_branch_name,order_account FROM (SELECT id,level_id,state,total_price,total_point,(total_price-realpay_amount)AS coupons_amount,realpay_amount,customer_user_id,orderNum,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,DATE_FORMAT(payment_time,'%Y-%m-%d %H:%i:%S') AS payment_time,merchant_id,order_id,trade_no,customeruservice_id,ownership FROM tb_order_info {condition})AS a LEFT JOIN (SELECT id,city_name AS order_city_name,district_name AS order_district_name,branch_name AS order_branch_name,account AS order_account FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT e.*,f.customer_level FROM (SELECT id,mobile,realname,level_id FROM tb_u_customer_user)AS e LEFT JOIN (SELECT id,CONCAT(prov_name,'-',city_name,'-',district_name,'-',branch_name)AS customer_level FROM tb_u_level)AS f ON e.level_id=f.id)AS d ON c.customer_user_id=d.id)AS g LEFT JOIN (SELECT GROUP_CONCAT(coupons_id)AS coupons_id,GROUP_CONCAT(status)AS coupons_status,GROUP_CONCAT(DATE_FORMAT(use_time,'%Y-%m-%d %H:%i:%S'))AS coupons_use_time,order_id FROM tb_goods_coupons WHERE order_id IN (SELECT id FROM tb_order_info {condition}) GROUP BY order_id)AS h ON g.id=h.order_id)AS l LEFT JOIN (SELECT GROUP_CONCAT(k.name)AS goods_name,GROUP_CONCAT(k.count)AS goods_count,GROUP_CONCAT(k.price)AS goods_price,order_id FROM (SELECT i.price,count,order_id,j.name FROM (SELECT * FROM tb_order_goods WHERE order_id IN (SELECT id FROM tb_order_info {condition}))AS i LEFT JOIN (SELECT * FROM tb_goods)AS j ON i.goods_id=j.id)AS k GROUP BY order_id)AS m ON l.id=m.order_id)AS p LEFT JOIN (SELECT n.*,o.merhant_acount,merchant_city_name,merhant_district_name,merchant_branch_name FROM (SELECT id,name AS merchant_name,wyfMerchantID,level_id FROM tb_merchant)AS n LEFT JOIN (SELECT id,city_name AS merchant_city_name,district_name AS merhant_district_name,branch_name AS merchant_branch_name,account AS merhant_acount FROM tb_u_level)AS o ON n.level_id=o.id)AS q ON p.merchant_id=q.id)AS r LEFT JOIN (SELECT id,mobile AS vice_mobile,realname AS vice_realname FROM tb_u_customer_user_vice WHERE id IN (SELECT customeruservice_id FROM tb_order_info {condition}))AS s ON r.customeruservice_id=s.id)AS t LEFT JOIN (SELECT receiver,province,city,district,place,mobile AS address_mobile,order_id FROM tb_order_address WHERE order_id IN (SELECT id FROM tb_order_info {condition}))AS u ON t.id=u.order_id;
            '''
            cursor.execute(sql)
        except Exception as e:
            sql = f'''
            SELECT p.*,q.account,level_name,merchant_name,wyfMerchantID FROM (SELECT l.*,m.goods_name,goods_count,goods_price FROM (SELECT g.*,h.coupons_id,coupons_status,coupons_use_time FROM (SELECT c.*,d.customer_level,mobile,realname FROM (SELECT a.*, b.name FROM (SELECT id,level_id,state,total_price,total_point,(total_price-realpay_amount)AS coupons_amount,realpay_amount,customer_user_id,orderNum,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S') AS create_time,merchant_id,order_id,trade_no FROM tb_order_info WHERE id=1)AS a LEFT JOIN (SELECT id,name FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT e.*,f.customer_level FROM (SELECT id,mobile,realname,level_id FROM tb_u_customer_user)AS e LEFT JOIN (SELECT id,CONCAT(prov_name,'-',city_name,'-',district_name,'-',branch_name)AS customer_level FROM tb_u_level)AS f ON e.level_id=f.id)AS d ON c.customer_user_id=d.id)AS g LEFT JOIN (SELECT GROUP_CONCAT(coupons_id)AS coupons_id,GROUP_CONCAT(status)AS coupons_status,GROUP_CONCAT(DATE_FORMAT(use_time,'%Y-%m-%d %H:%i:%S'))AS coupons_use_time,order_id FROM tb_goods_coupons GROUP BY order_id)AS h ON g.id=h.order_id)AS l LEFT JOIN (SELECT GROUP_CONCAT(k.name)AS goods_name,GROUP_CONCAT(k.count)AS goods_count,GROUP_CONCAT(k.price)AS goods_price,order_id FROM (SELECT i.price,count,order_id,j.name FROM (SELECT * FROM tb_order_goods)AS i LEFT JOIN (SELECT * FROM tb_goods)AS j ON i.goods_id=j.id)AS k GROUP BY order_id)AS m ON l.id=m.order_id)AS p LEFT JOIN (SELECT n.*,o.account,level_name FROM (SELECT id,name AS merchant_name,wyfMerchantID,level_id FROM tb_merchant)AS n LEFT JOIN (SELECT id,name AS level_name,account FROM tb_u_level)AS o ON n.level_id=o.id)AS q ON p.merchant_id=q.id;
            '''
            cursor.execute(sql)
        ziduan = ['id', '平台订单号', '订单号', '支付流水号', '下单时间', '商品名称', '单价', '数量', '总金额(元)', '商家信息', '微邮付商户号', '商家所属市公司', '商家所属区公司', '商家所属网点', '商家所属机构号', '购买市公司', '购买区公司', '购买网点', '购买机构号', '订单状态', '付款时间', '邮米抵扣', '优惠券抵扣(元)', '实付金额(元)', '券码', '券码状态', '券码核销时间', '购买用户手机号', '用户真实姓名', '用户归属', '收货人', '收货人手机号', '收货地址', '小邮美宿标识']
        cols = ['id', 'order_id', 'orderNum', 'trade_no', 'create_time', 'goods_name', 'goods_price', 'goods_count', 'total_price', 'merchant_name', 'wyfMerchantID', 'merchant_city_name', 'merhant_district_name', 'merchant_branch_name', 'merhant_acount', 'order_city_name', 'order_district_name', 'order_branch_name', 'order_account', 'state', 'payment_time', 'total_point', 'coupons_amount', 'realpay_amount', 'coupons_id', 'coupons_status', 'coupons_use_time', 'mobile', 'realname', 'customer_level', 'receiver', 'address_mobile', 'place', 'ownership']
        response = StreamingHttpResponse(self.get_orderinfo_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('订单列表.csv'))
        return response

    def get_orderinfo_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATUS_ENUM = {
            0: '待付款',
            1: '待付款',
            2: '待核销',
            3: '已核销',
            4: '订单过期',
            5: '售后中',
            6: '已发货',
            7: '已预约',
            8: '已退款',
            9: '发货中',
            10: '退款中',
            11: '订单异常',
            12: '已付款',
            13: '已取消',
            14: '已完成',
            15: '待成团'
        }
        COUPONS_STATUS_ENUM = {
            "0": '未激活',
            "1": '待核销',
            "2": '已核销',
            "3": '已过期',
            "4": '已撤销'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'state':
                    tmp_str += '"%s",' % (STATUS_ENUM[row[col]])
                elif col == 'coupons_status':
                    coupons_status_str = ''
                    if row[col]:
                        coupons_status_list = row[col].split(',')
                        for coupons_status in coupons_status_list:
                            coupons_status_str += COUPONS_STATUS_ENUM[coupons_status]
                            coupons_status_str += ','
                    tmp_str += '"%s",' % (coupons_status_str)
                elif col in ['mobile', 'realname']:
                    if row['customeruservice_id']:
                        tmp_str += '"%s",' % (str(row[f'vice_{col}']).encode('GBK', 'ignore').decode('gbk') if row[f'vice_{col}'] else '')
                    else:
                        tmp_str += '"%s",' % (str(row[f'{col}']).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'place':
                    tmp_str += '"%s",' % (str(f'{row["province"]}-{row["city"]}-{row["district"]}-{row[col]}').encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col in ['orderNum', 'trade_no', 'coupons_id', 'order_id']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col in ['total_price', 'total_point', 'coupons_amount', 'realpay_amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.state != 1:
            return Response({'error': '订单状态错误'}, status=status.HTTP_400_BAD_REQUEST)
        ordergoods_set = instance.ordergoods_set.all()
        for ordergoods in ordergoods_set:
            goods = ordergoods.goods
            launched_goods = ordergoods.launched_goods
            count = ordergoods.count
            origin_sales = goods.sales
            origin_remain_stock = goods.remain_stock
            new_sales = origin_sales - count
            new_remain_stock = origin_remain_stock + count
            goods.remain_stock = new_remain_stock
            goods.sales = new_sales
            goods.save()
            if launched_goods:
                launched_origin_stock = launched_goods.stock
                launched_origin_sales = launched_goods.sales
                launched_new_stock = launched_origin_stock + count
                launched_new_sales = launched_origin_sales - count
                launched_goods.stock = launched_new_stock
                launched_goods.sales = launched_new_sales
                launched_goods.save()
            else:
                origin_stock = goods.stock
                new_stock = origin_stock + count
                goods.stock = new_stock
                goods.save()
        instance.state = 13
        instance.cancel_time = datetime.datetime.now()
        instance.total_point = 0
        instance.save()
        alidiscounponinfo_set = instance.alidiscountcouponinfo_set.all().filter(status=8).update(status=1, order_id=None)
        try:
            user = instance.customer_user
            pointrecord_set = instance.pointrecord_set.all()
            for pointrecord in pointrecord_set:
                activity_data = pointrecord.activity_data
                activity_data.remain_point += pointrecord.point
                activity_data.state = 3
                activity_data.save()
                user.point += pointrecord.point
                user.save()
                in_pointrecord_data = {}
                in_pointrecord_data['customer_user_id'] = user.id
                in_pointrecord_data['create_time'] = datetime.datetime.now()
                in_pointrecord_data['operate_type'] = 1
                in_pointrecord_data['business_type'] = 4
                in_pointrecord_data['point'] = pointrecord.point
                in_pointrecord_data['point_balance'] = user.point
                in_pointrecord_data['remain_point'] = activity_data.remain_point
                in_pointrecord_data['activity_data_id'] = activity_data.id
                in_pointrecord_data['order_id'] = instance.id
                PointRecord.objects.create(**in_pointrecord_data)
        except:
            pass
        return Response({'success': '取消订单成功'}, status=status.HTTP_200_OK)

    def update_fund_trans(self, request, *args, **kwargs):
        fund_id = self.request.data.get('fund_id', None)
        identity = self.request.data.get('identity', None)
        name = self.request.data.get('name', None)
        if not all([fund_id, identity, name]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            orderfundtrans = OrderFundTrans.objects.get(id=fund_id)
        except:
            return Response({'error': '无效转账id'}, status=status.HTTP_400_BAD_REQUEST)
        if orderfundtrans.status == 2:
            return Response({'error': '已转账成功,不可更改转账信息'}, status=status.HTTP_400_BAD_REQUEST)
        if orderfundtrans.status == 3:
            return Response({'error': '转账处理中,不可更改转账信息'}, status=status.HTTP_400_BAD_REQUEST)
        origin_identity = orderfundtrans.identity
        origin_name = orderfundtrans.name
        if origin_identity == identity and origin_name == name:
            return Response({'error': '转账信息无更正'}, status=status.HTTP_400_BAD_REQUEST)
        orderfundtrans.identity = identity
        orderfundtrans.name = name
        orderfundtrans.sub_msg = None
        if orderfundtrans.status == 4:
            origin_out_biz_no = orderfundtrans.out_biz_no
            new_out_biz_no = f'{int(origin_out_biz_no[:1])+1}{origin_out_biz_no[1:]}'
            orderfundtrans.out_biz_no = new_out_biz_no
        orderfundtrans.save()
        order = orderfundtrans.order
        if order.state == 12:
            alipay_obj = C_AlipayOpen()
            trans_amount = str(orderfundtrans.exchange_amount)
            order_title = orderfundtrans.order_title
            out_biz_no = orderfundtrans.out_biz_no
            identity = orderfundtrans.identity
            if orderfundtrans.identity_type == 1:
                result = json.loads(alipay_obj.transunitransfer_user_id(identity, out_biz_no, trans_amount, order_title))
            else:
                name = orderfundtrans.name
                result = json.loads(alipay_obj.transunitransfer_login_id(identity, name, out_biz_no, trans_amount, order_title))
            if result['code'] != '10000':
                orderfundtrans.sub_msg = result['sub_msg']
                orderfundtrans.status = 4
                orderfundtrans.save()
                # try:
                #     ali_fundtrans_template(order.id, str(trans_amount), result['sub_msg'])
                # except:
                #     pass
                return Response({'success': '修改转账信息成功,转账信息仍然有误,转账失败'}, status=status.HTTP_200_OK)
            else:
                orderfundtrans.ali_order_id = result['order_id']
                orderfundtrans.pay_fund_order_id = result['pay_fund_order_id']
                orderfundtrans.trans_date = result['trans_date']
                orderfundtrans.status = 2
                orderfundtrans.save()
                return Response({'success': '修改转账信息成功,并已转账成功'}, status=status.HTTP_200_OK)
        else:
            return Response({'success': '修改转账信息成功'}, status=status.HTTP_200_OK)

    def synchronization_garbage_data(self, request):
        user = self.request.iser
        my_file = self.request.FILES.get('my_file', None)
        start = self.request.data.get('start', None)
        end = self.request.data.get('end', None)
        if not all([start, end]):
            return Response({'error': '请上传交易区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            datetime.datetime.strptime(start, '%Y-%m-%d %H:%M:%S')
            datetime.datetime.strptime(end, '%Y-%m-%d %H:%M:%S')
        except:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        title = table.row_values(0)
        if '商户号' not in title:
            return Response({'error': '表格字段缺少"商户号"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        history = UpdateStockUsableMerchant.objects.create(user_id=user.id, file=file, add_type=3)
        synchronization_garbage_data.delay({'id': history.id, 'start': start, 'end': end})
        # synchronization_garbage_data({'id': history.id, 'start': start, 'end': end})
        # update_stock_usable_merchant({'id': history.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)

    def synchronization_garbage_order(self, request):
        user = self.request.iser
        start = self.request.data.get('start', None)
        end = self.request.data.get('end', None)
        merchant_id = self.request.data.get('merchant_id', None)
        if not all([start, end]):
            return Response({'error': '请上传交易区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            start1 = datetime.datetime.strptime(start, '%Y-%m-%d')
            end1 = datetime.datetime.strptime(end, '%Y-%m-%d')
        except:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if start1 > end1:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if merchant_id:
            try:
                merchant = Merchant.objects.get(id=merchant_id)
            except:
                return Response({'error': '错误商户号'}, status=status.HTTP_400_BAD_REQUEST)
            business_no = merchant.wyfMerchantID
        else:
            business_no = None
        synchronization_garbage_order.delay({'business_no': business_no, 'start': start, 'end': end})
        # synchronization_garbage_order({'business_no': business_no, 'start': start, 'end': end})
        return Response({'success': '系统同步交易中'}, status=status.HTTP_200_OK)

    def statistical_order_date(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        if time_max1 - time_min1 > 2678400:
            return Response({'error': '时区不得大于31天!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        order_amount_data = {}
        order_num_data = {}
        use_amount_data = {}
        use_num_data = {}
        data1 = []
        data2 = []
        data3 = []
        data4 = []
        data = {}
        max_num = 0
        max_amount = 0
        order_amount_sum = 0
        order_num_sum = 0
        use_amount_sum = 0
        use_num_sum = 0
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if time_min == time_max:
            xAxis = ['0时', '1时', '2时', '3时', '4时', '5时', '6时', '7时', '8时', '9时', '10时', '11时', '12时', '13时', '14时', '15时', '16时', '17时', '18时', '19时', '20时', '21时', '22时', '23时']
            a = 0
            b = 0
            sql = f'''SELECT DATE_FORMAT(create_time,'%H')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            create_result = cursor.fetchall()
            sql = f'''SELECT DATE_FORMAT(use_time,'%H')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            use_result = cursor.fetchall()
            if create_result:
                while True:
                    if int(create_result[0]['time']) <= b:
                        break
                    data3.append(0)
                    data4.append(0)
                    b += 1
                for x in create_result:
                    while True:
                        if int(x['time']) < b:
                            break
                        if int(x['time']) == b:
                            data3.append(x['total_price__sum'])
                            data4.append(x['id__count'])
                            order_num_sum += x['id__count']
                            order_amount_sum += x['total_price__sum']
                            b += 1
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data3.append(0)
                            data4.append(0)
                            b += 1
                while True:
                    if len(data3) >= 24:
                        break
                    data3.append(0)
                    data4.append(0)
            else:
                for i in range(24):
                    data3.append(0)
                    data4.append(0)
            if use_result:
                while True:
                    if int(use_result[0]['time']) <= a:
                        break
                    data1.append(0)
                    data2.append(0)
                    a += 1
                for x in use_result:
                    while True:
                        if int(x['time']) < a:
                            break
                        if int(x['time']) == a:
                            data1.append(x['total_price__sum'])
                            data2.append(x['id__count'])
                            use_num_sum += x['id__count']
                            use_amount_sum += x['total_price__sum']
                            a += 1
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data1.append(0)
                            data2.append(0)
                            a += 1
                while True:
                    if len(data1) >= 24:
                        break
                    data1.append(0)
                    data2.append(0)
            else:
                for i in range(24):
                    data1.append(0)
                    data2.append(0)
            use_amount_data['name'] = '核销金额'
            use_amount_data['data'] = data1
            use_num_data['name'] = '核销笔数'
            use_num_data['data'] = data2
            order_amount_data['name'] = '下单金额'
            order_amount_data['data'] = data3
            order_num_data['name'] = '下单笔数'
            order_num_data['data'] = data4
        else:
            xAxis = []
            time_min = datetime.datetime.strptime(time_min, "%Y-%m-%d").date()
            time_max = datetime.datetime.strptime(time_max, "%Y-%m-%d").date()
            create_begin_time = time_min
            use_begin_time = time_min
            days_num = (time_max-time_min).days + 1
            begin_day = time_min
            sql = f'''SELECT DATE_FORMAT(create_time,'%Y-%m-%d')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            create_result = cursor.fetchall()
            sql = f'''SELECT DATE_FORMAT(use_time,'%Y-%m-%d')AS time,COUNT(id)AS id__count,SUM(total_price)AS total_price__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time;'''
            cursor.execute(sql)
            use_result = cursor.fetchall()
            while True:
                if begin_day > time_max:
                    break
                xAxis.append(datetime.datetime.strftime(begin_day, '%m/%d'))
                begin_day += datetime.timedelta(days=1)
            if create_result:
                while True:
                    if datetime.datetime.strptime(create_result[0]['time'], "%Y-%m-%d").date() <= create_begin_time:
                        break
                    data3.append(0)
                    data4.append(0)
                    create_begin_time += datetime.timedelta(days=1)
                for x in create_result:
                    while True:
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() < create_begin_time:
                            break
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() == create_begin_time:
                            data3.append(x['total_price__sum'])
                            data4.append(x['id__count'])
                            order_num_sum += x['id__count']
                            order_amount_sum += x['total_price__sum']
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data3.append(0)
                            data4.append(0)
                        create_begin_time += datetime.timedelta(days=1)
                while True:
                    if len(data3) >= days_num:
                        break
                    data3.append(0)
                    data4.append(0)
            else:
                for i in range(days_num):
                    data3.append(0)
                    data4.append(0)
            if use_result:
                while True:
                    if datetime.datetime.strptime(use_result[0]['time'], "%Y-%m-%d").date() <= use_begin_time:
                        break
                    data1.append(0)
                    data2.append(0)
                    use_begin_time += datetime.timedelta(days=1)
                for x in use_result:
                    while True:
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() < use_begin_time:
                            break
                        if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() == use_begin_time:
                            data1.append(x['total_price__sum'])
                            data2.append(x['id__count'])
                            use_num_sum += x['id__count']
                            use_amount_sum += x['total_price__sum']
                            if max_amount < x['total_price__sum']:
                                max_amount = x['total_price__sum']
                            if max_num < x['id__count']:
                                max_num = x['id__count']
                        else:
                            data1.append(0)
                            data2.append(0)
                        use_begin_time += datetime.timedelta(days=1)
                while True:
                    if len(data1) >= days_num:
                        break
                    data1.append(0)
                    data2.append(0)
            else:
                for i in range(days_num):
                    data1.append(0)
                    data2.append(0)
            use_amount_data['name'] = '核销金额'
            use_amount_data['data'] = data1
            use_num_data['name'] = '核销笔数'
            use_num_data['data'] = data2
            order_amount_data['name'] = '下单金额'
            order_amount_data['data'] = data3
            order_num_data['name'] = '下单笔数'
            order_num_data['data'] = data4
        cursor.close()
        conn.close()
        if max_amount:
            str_max_amount = str(max_amount)
            str_max_num = str(max_num)
            if '.' in str_max_amount:
                len_max_amount = len(str_max_amount[:str_max_amount.find('.')])
            else:
                len_max_amount = len(str_max_amount)
            if '.' in str_max_num:
                len_max_num = len(str_max_num[:str_max_num.find('.')])
            else:
                len_max_num = len(str_max_num)
            to_max_amount = '1'
            to_max_num = '1'
            while True:
                if len(to_max_amount) >= len_max_amount -1:
                    to_max_amount = int(to_max_amount)
                    break
                to_max_amount += '0'
            while True:
                if len(to_max_num) >= len_max_num -1:
                    to_max_num = int(to_max_num)
                    break
                to_max_num += '0'
            max_amount = math.ceil(max_amount/to_max_amount)*to_max_amount
            max_num = math.ceil(max_num/to_max_num)*to_max_num
        data['order_amount_data'] = order_amount_data
        data['order_num_data'] = order_num_data
        data['use_amount_data'] = use_amount_data
        data['use_num_data'] = use_num_data
        data['max_amount'] = max_amount
        data['max_num'] = max_num
        data['xAxis'] = xAxis
        data['use_amount_sum'] = use_amount_sum
        data['use_num_sum'] = use_num_sum
        data['order_amount_sum'] = order_amount_sum
        data['order_num_sum'] = order_num_sum
        return Response(data, status=status.HTTP_200_OK)

    def export_statistical_order_date(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if time_min == time_max:
            sql = f'''
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%H时')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a LEFT JOIN (SELECT DATE_FORMAT(use_time,'%H时')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time) 
            UNION
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%H时')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a RIGHT JOIN (SELECT DATE_FORMAT(use_time,'%H时')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time);
            '''
        else:
            sql = f'''
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%Y-%m-%d')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a LEFT JOIN (SELECT DATE_FORMAT(use_time,'%Y-%m-%d')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time)
            UNION
            (SELECT a.*,b.use__count,use__sum,b.time AS c_time FROM (SELECT DATE_FORMAT(create_time,'%Y-%m-%d')AS time,COUNT(id)AS order__count,SUM(total_price)AS order__sum FROM tb_order_info WHERE create_time>="{time_min1}" AND create_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS a RIGHT JOIN (SELECT DATE_FORMAT(use_time,'%Y-%m-%d')AS time,COUNT(id)AS use__count,SUM(total_price)AS use__sum FROM tb_order_info WHERE use_time>="{time_min1}" AND use_time<="{time_max1}" AND order_species=1 {condition} GROUP BY time)AS b ON a.time=b.time);
            '''
        cursor.execute(sql)
        ziduan = ['日期', '机构名称', '邮米下单金额', '邮米下单笔数', '邮米核销金额', '邮米核销笔数']
        cols = ['time', 'name', 'order__sum', 'order__count', 'use__sum', 'use__count']
        response = StreamingHttpResponse(self.get_statistical_order_date_fromat(cols, cursor, conn, ziduan, level.name))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('邮米结算统计.csv'))
        return response

    def get_statistical_order_date_fromat(self, cols, cursor, conn, ziduan, level_name):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'name':
                    tmp_str += '"%s",' % (level_name)
                elif col == 'time':
                    tmp_str += '"%s",' % (str(row[col]) if row[col] else row['c_time'])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
            yield tmp_str.strip(',') + "\n"

    def statistical_order_level(self, request):
        level_id = self.request.query_params.get('level', None)
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        dimensionality = self.request.query_params.get('dimensionality', '1')
        user = self.request.iser
        level = user.level
        level_level_id = level.level
        level_list = get_level_list(level)
        if level_id:
            if int(level_id) not in level_list:
                return Response({'error': '您无权限查看当前机构'}, status=status.HTTP_400_BAD_REQUEST)
            level = Level.objects.get(id=level_id)
            level_list = get_level_list(level)
            level_level_id = level.level
        condition = 'WHERE order_species=1'
        if time_min or time_max:
            try:
                time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
                time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            except:
                return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
            time_min = time_min + ' 00:00:00'
            time_max = time_max + ' 23:59:59'
            # create_queryset = queryset.filter(create_time__gte=time_min, create_time__lte=time_max)
            # use_queryset = queryset.filter(use_time__gte=time_min, use_time__lte=time_max)
            create_condition = condition + f' AND create_time>="{time_min}" AND create_time<="{time_max}"'
            use_condition = condition + f' AND use_time>="{time_min}" AND use_time<="{time_max}"'
        else:
            # create_queryset = queryset
            # use_queryset = queryset
            create_condition = condition
            use_condition = condition
        if level_level_id not in [0, 1, 2] and dimensionality == '1':
            return Response({'error': '当前机构等级不能查看市级数据统计'}, status=status.HTTP_400_BAD_REQUEST)
        if level_level_id not in [0, 1, 2, 3] and dimensionality == '2':
            return Response({'error': '当前机构等级不能查看区县级数据统计'}, status=status.HTTP_400_BAD_REQUEST)
        level_query = Level.objects.filter(id__in=level_list)
        data = []
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if str(dimensionality) == '1':
            level_query = level_query.filter(level=2)
            # page = self.paginate_queryset(level_query)
            # sql_level_list = []
            # for level in page:
            #     sql_level_list.append(level.id)
            sql_level_list = list(level_query.values_list('id', flat=True))
            if len(sql_level_list) == 1:
                sql_level_list = str(tuple(sql_level_list)).replace(',', '')
            else:
                sql_level_list = str(tuple(sql_level_list))
            create_condition = create_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id IN {sql_level_list})'
            use_condition = use_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id IN {sql_level_list})'
            sql = f'''
            SELECT * FROM (SELECT e.*,f.order_num_sum,order_amount_sum FROM (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level WHERE id IN {sql_level_list})AS e LEFT JOIN (SELECT COUNT(c.id)AS order_num_sum,SUM(c.total_price)AS order_amount_sum,MAX(c.city_id)AS city_id FROM (SELECT a.id,total_price,b.city_id FROM (SELECT id,total_price,level_id FROM tb_order_info {create_condition})AS a LEFT JOIN (SELECT id,city_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.city_id)AS f ON e.id=f.city_id)AS g LEFT JOIN (SELECT COUNT(c.id)AS use_num_sum,SUM(c.total_price)AS use_amount_sum,MAX(c.city_id)AS city_id FROM (SELECT a.id,total_price,b.city_id FROM (SELECT id,total_price,level_id FROM tb_order_info {use_condition})AS a LEFT JOIN (SELECT id,city_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.city_id)AS h ON g.id=h.city_id;
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            for row in rows:
                dict1 = {}
                dict1['id'] = row['id']
                dict1['province'] = row['prov_name']
                dict1['city'] = row['city_name']
                dict1['district'] = ''
                dict1['branch'] = ''
                dict1['use_amount_sum'] = row['use_amount_sum'] if row['use_amount_sum'] else 0
                dict1['use_num_sum'] = row['use_num_sum'] if row['use_num_sum'] else 0
                dict1['order_amount_sum'] = row['order_amount_sum'] if row['order_amount_sum'] else 0
                dict1['order_num_sum'] = row['order_num_sum'] if row['order_num_sum'] else 0
                data.append(dict1)
            return Response(data, status=status.HTTP_200_OK)
            return self.get_paginated_response(data)
        elif str(dimensionality) == '2':
            level_query = level_query.filter(level=3)
            # page = self.paginate_queryset(level_query)
            # sql_level_list = []
            # for level in page:
            #     sql_level_list.append(level.id)
            sql_level_list = list(level_query.values_list('id', flat=True))
            if len(sql_level_list) == 1:
                sql_level_list = str(tuple(sql_level_list)).replace(',', '')
            else:
                sql_level_list = str(tuple(sql_level_list))
            create_condition = create_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id IN {sql_level_list})'
            use_condition = use_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id IN {sql_level_list})'
            sql = f'''
            SELECT * FROM (SELECT e.*,f.order_num_sum,order_amount_sum FROM (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level WHERE id IN {sql_level_list})AS e LEFT JOIN (SELECT COUNT(c.id)AS order_num_sum,SUM(c.total_price)AS order_amount_sum,MAX(c.district_id)AS district_id FROM (SELECT a.id,total_price,b.district_id FROM (SELECT id,total_price,level_id FROM tb_order_info {create_condition})AS a LEFT JOIN (SELECT id,district_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.district_id)AS f ON e.id=f.district_id)AS g LEFT JOIN (SELECT COUNT(c.id)AS use_num_sum,SUM(c.total_price)AS use_amount_sum,MAX(c.district_id)AS district_id FROM (SELECT a.id,total_price,b.district_id FROM (SELECT id,total_price,level_id FROM tb_order_info {use_condition})AS a LEFT JOIN (SELECT id,district_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.district_id)AS h ON g.id=h.district_id;
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            for row in rows:
                dict1 = {}
                dict1['id'] = row['id']
                dict1['province'] = row['prov_name']
                dict1['city'] = row['city_name']
                dict1['district'] = row['district_name']
                dict1['branch'] = ''
                dict1['use_amount_sum'] = row['use_amount_sum'] if row['use_amount_sum'] else 0
                dict1['use_num_sum'] = row['use_num_sum'] if row['use_num_sum'] else 0
                dict1['order_amount_sum'] = row['order_amount_sum'] if row['order_amount_sum'] else 0
                dict1['order_num_sum'] = row['order_num_sum'] if row['order_num_sum'] else 0
                data.append(dict1)
            return Response(data, status=status.HTTP_200_OK)
            # return self.get_paginated_response(data)
        else:
            level_query = level_query.filter(level=4)
            # page = self.paginate_queryset(level_query)
            # for level in page:
            #     dict1 = {}
            #     dict1['id'] = level.id
            #     dict1['province'] = level.parent.parent.parent.name
            #     dict1['city'] = level.parent.parent.name
            #     dict1['district'] = level.parent.name
            #     dict1['branch'] = level.name
            #     use_queryset1 = use_queryset.filter(level_id=level.id)
            #     create_queryset1 = create_queryset.filter(level_id=level.id)
            #     try:
            #         total_price__sum = use_queryset1.aggregate(Sum('total_price'))['total_price__sum']
            #         dict1['use_amount_sum'] = total_price__sum if total_price__sum else 0
            #         dict1['use_num_sum'] = use_queryset1.count()
            #     except:
            #         dict1['use_amount_sum'] = 0
            #         dict1['use_num_sum'] = 0
            #     try:
            #         total_price__sum = create_queryset1.aggregate(Sum('total_price'))['total_price__sum']
            #         dict1['order_amount_sum'] = total_price__sum if total_price__sum else 0
            #         dict1['order_num_sum'] = create_queryset1.count()
            #     except:
            #         dict1['order_amount_sum'] = 0
            #         dict1['order_num_sum'] = 0
            #     data.append(dict1)
            # return self.get_paginated_response(data)
            sql_level_list = list(level_query.values_list('id', flat=True))
            if len(sql_level_list) == 1:
                sql_level_list = str(tuple(sql_level_list)).replace(',', '')
            else:
                sql_level_list = str(tuple(sql_level_list))
            create_condition = create_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id IN {sql_level_list})'
            use_condition = use_condition + f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id IN {sql_level_list})'
            sql = f'''
            SELECT * FROM (SELECT e.*,f.order_num_sum,order_amount_sum FROM (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level WHERE id IN {sql_level_list})AS e LEFT JOIN (SELECT COUNT(c.id)AS order_num_sum,SUM(c.total_price)AS order_amount_sum,MAX(c.branch_id)AS branch_id FROM (SELECT a.id,total_price,b.branch_id FROM (SELECT id,total_price,level_id FROM tb_order_info {create_condition})AS a LEFT JOIN (SELECT id,branch_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.branch_id)AS f ON e.id=f.branch_id)AS g LEFT JOIN (SELECT COUNT(c.id)AS use_num_sum,SUM(c.total_price)AS use_amount_sum,MAX(c.branch_id)AS branch_id FROM (SELECT a.id,total_price,b.branch_id FROM (SELECT id,total_price,level_id FROM tb_order_info {use_condition})AS a LEFT JOIN (SELECT id,branch_id FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.branch_id)AS h ON g.id=h.branch_id;
            '''
            cursor.execute(sql)
            rows = cursor.fetchall()
            cursor.close()
            conn.close()
            for row in rows:
                dict1 = {}
                dict1['id'] = row['id']
                dict1['province'] = row['prov_name']
                dict1['city'] = row['city_name']
                dict1['district'] = row['district_name']
                dict1['branch'] = row['branch_name']
                dict1['use_amount_sum'] = row['use_amount_sum'] if row['use_amount_sum'] else 0
                dict1['use_num_sum'] = row['use_num_sum'] if row['use_num_sum'] else 0
                dict1['order_amount_sum'] = row['order_amount_sum'] if row['order_amount_sum'] else 0
                dict1['order_num_sum'] = row['order_num_sum'] if row['order_num_sum'] else 0
                data.append(dict1)
            return Response(data, status=status.HTTP_200_OK)

    def export_statistical_order_level(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        dimensionality = self.request.query_params.get('dimensionality', '1')
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        period = f'{time_min}~{time_max}'
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        if dimensionality == '1':
            keywork = 'city_name'
        elif dimensionality == '2':
            keywork = 'district_name'
        else:
            keywork = 'branch_name'
        sql = f'''
        (SELECT e.*,f.use_count,use_sum,f.prov_name AS use_prov_name,f.city_name AS use_city_name,f.district_name AS use_district_name,f.branch_name AS use_branch_name FROM (SELECT COUNT(c.id)AS order_count,SUM(c.total_price)AS order_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND create_time>="{time_min1}" AND create_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS e LEFT JOIN (SELECT COUNT(c.id)AS use_count,SUM(c.total_price)AS use_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND use_time>="{time_min1}" AND use_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS f ON e.{keywork}=f.{keywork})
        UNION
        (SELECT e.*,f.use_count,use_sum,f.prov_name AS use_prov_name,f.city_name AS use_city_name,f.district_name AS use_district_name,f.branch_name AS use_branch_name FROM (SELECT COUNT(c.id)AS order_count,SUM(c.total_price)AS order_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND create_time>="{time_min1}" AND create_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS e RIGHT JOIN (SELECT COUNT(c.id)AS use_count,SUM(c.total_price)AS use_sum,MAX(c.prov_name)AS prov_name,MAX(c.city_name)AS city_name,MAX(c.district_name)AS district_name,MAX(c.branch_name)AS branch_name FROM (SELECT a.id,total_price,b.prov_name,city_name,district_name,branch_name FROM (SELECT id,total_price,level_id FROM tb_order_info WHERE order_species=1 AND use_time>="{time_min1}" AND use_time<="{time_max1}" {condition})AS a LEFT JOIN (SELECT id,prov_name,city_name,district_name,branch_name FROM tb_u_level)AS b ON a.level_id=b.id)AS c GROUP BY c.{keywork})AS f ON e.{keywork}=f.{keywork})
        '''
        cursor.execute(sql)
        ziduan = ['周期', '市公司', '区县公司', '网点', '下单金额', '下单笔数', '核销金额', '核销笔数']
        cols = ['period', 'city_name', 'district_name', 'branch_name', 'order_sum', 'order_count', 'use_sum', 'use_count']
        response = StreamingHttpResponse(self.get_statistical_order_level_fromat(cols, cursor, conn, ziduan, period, dimensionality))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('机构数据统计.csv'))
        return response

    def get_statistical_order_level_fromat(self, cols, cursor, conn, ziduan, period, dimensionality):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'period':
                    tmp_str += '"%s",' % (period)
                elif col == 'city_name':
                    tmp_str += '"%s",' % (row[col] if row[col] else row['use_city_name'])
                elif col == 'district_name':
                    tmp_str += '"%s",' % ('' if dimensionality == '1' else (row[col] if row[col] else row['use_district_name']))
                elif col == 'branch_name':
                    tmp_str += '"%s",' % ((row[col] if row[col] else row['use_branch_name']) if dimensionality == '3' else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
            yield tmp_str.strip(',') + "\n"

    def add_express(self, request):
        user = self.request.iser
        express_array = self.request.data.get('express_array', None)
        order_id = self.request.data.get('order_id', None)
        if not all([express_array, order_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=order_id)
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.state not in [6, 9]:
            return Response({'error': '订单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                for express in express_array:
                    try:
                        ExpressType.objects.get(id=express['express_type_id'])
                    except:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '无效快递类型'}, status=status.HTTP_400_BAD_REQUEST)
                    express_data = {}
                    express_data['express_type_id'] = express['express_type_id']
                    express_data['express_number'] = express['express_number']
                    express, create = Express.objects.get_or_create(defaults=express_data, express_number=express['express_number'])
                    relevance_data = {}
                    relevance_data['order_id'] = instance.id
                    relevance_data['express_id'] = express.id
                    ExpressOrderRelevance.objects.get_or_create(defaults=relevance_data, order_id=relevance_data['order_id'], express_id=relevance_data['express_id'])
                instance.state = 6
                instance.save()
                OrderAdvance.objects.create(
                    order_id=instance.id,
                    status=3,
                    remark='添加快递单号',
                    user_id=user.id
                )
                try:
                    express_send.delay({'id': instance.id, 'express_id': express.id})
                except:
                    pass
            except Exception as e:
                logger.error(e)
                transaction.savepoint_rollback(save_id)
                return Response({'error': '操作失败'}, status=status.HTTP_400_BAD_REQUEST)
            transaction.savepoint_commit(save_id)
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def delete_express(self,request):
        user = self.request.iser
        express_id = self.request.data.get('express_id', None)
        order_id = self.request.data.get('order_id', None)
        if not all([order_id, express_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=order_id)
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        delete_instance = ExpressOrderRelevance.objects.filter(order_id=order_id, express_id=express_id)
        if not delete_instance:
            return Response({'error': '解绑失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        delete_instance.delete()
        check_relevance = ExpressOrderRelevance.objects.filter(order_id=order_id)
        if not check_relevance:
            instance.state = 9
            instance.save()
        OrderAdvance.objects.create(
            order_id=instance.id,
            status=3,
            remark='解绑快递单号',
            user_id=user.id
        )
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def refund_order(self, request):
        user = self.request.iser
        order_id = self.request.data.get('order_id', None)
        refund_money = self.request.data.get('refund_money', None)
        remark = self.request.data.get('remark', None)
        refund_type = self.request.data.get('refund_type', None)
        if not order_id:
            return Response({'error': '请上传订单id'}, status=status.HTTP_400_BAD_REQUEST)
        order = self.get_queryset().filter(id=order_id).first()
        if order.state != 9:
            return Response({'error': '仅发货中的订单可以退款'}, status=status.HTTP_400_BAD_REQUEST)
        if user.id not in [1, 12105, 9297, 65139]:
            return Response({'error': '您无权限操作'}, status=status.HTTP_400_BAD_REQUEST)
        if order.order_species != 2:
            return Response({'error': '仅支持微邮惠商圈订单退款'}, status=status.HTTP_400_BAD_REQUEST)
        url = 'https://api.gdwxyf.com/api/refund.do'
        datetime_now = datetime.datetime.now()
        order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
        if str(refund_type) == '1':
            body = {
                "program_id": "202316837141378884",
                "shop_id": order.merchant.ruiyinxinMerchantID,
                "refund_order_id": order_id,
                "order_id": order.orderNum
            }
        else:
            return Response({'error': '暂不支持部分退款'}, status=status.HTTP_400_BAD_REQUEST)
            body = {
                "program_id": "202316837141378884",
                "shop_id": order.merchant.ruiyinxinMerchantID,
                "refund_order_id": order_id,
                "order_id": order.orderNum
            }
        key = '6715C7D4435343BA0459EAEC2334D81B'
        sign = get_sign(body, key)
        body['sign'] = sign
        response = requests.post(url=url, json=body)
        result = response.json()
        if result['result_code'] == 'success':
            refund_order_data = {}
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
            refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
            refund_order_data['refund_money'] = result['refund_money']
            refund_order_data['refund_procedure_money'] = result['commission']
            refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            order.state = 8
            # order.refund_money += Decimal(result['refund_money'])
            order.refund_money = order.realpay_amount
            order.out_refund_no = order_id
            order.refund_id = result['refund_transaction_id']
            order.save()
            try:
                after_sale = AfterSaleApplicationForm.objects.filter(order_id=order.id, form_status=1).last()
                if after_sale:
                    after_sale.form_status = 2
                    after_sale.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    after_sale.save()
            except:
                pass
            ordergoods_set = order.ordergoods_set.all()
            for ordergoods in ordergoods_set:
                goods = ordergoods.goods
                launched_goods = ordergoods.launched_goods
                count = ordergoods.count
                origin_sales = goods.sales
                origin_remain_stock = goods.remain_stock
                new_sales = origin_sales - count
                new_remain_stock = origin_remain_stock + count
                goods.sales = new_sales
                goods.remain_stock = new_remain_stock
                goods.save()
                if launched_goods:
                    launched_origin_stock = launched_goods.stock
                    launched_origin_sales = launched_goods.sales
                    launched_new_stock = launched_origin_stock + count
                    launched_new_sales = launched_origin_sales - count
                    launched_goods.stock = launched_new_stock
                    launched_goods.sales = launched_new_sales
                    launched_goods.save()
                else:
                    origin_stock = goods.stock
                    new_stock = origin_stock + count
                    goods.stock = new_stock
                    goods.save()

            return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
        else:
            refund_order_data = {}
            refund_order_data['refund_status'] = 2
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['return_msg'] = result['error_msg']
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_time'] = datetime_now
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            return Response({'error': f'退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)

    def batch_add_express(self, request):
        user = self.request.iser
        check_history = ExpressQuest.objects.filter(is_enter=1)
        if check_history:
            is_enter = 4
        else:
            is_enter = 1
        my_file = self.request.FILES.get('my_file', None)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        nrows = table.nrows - 1
        title = table.row_values(0)
        if '订单号' not in title:
            return Response({'error': '表格字段缺少"订单号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '快递单号' not in title:
            return Response({'error': '表格字段缺少"快递单号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '快递类型' not in title:
            return Response({'error': '表格字段缺少"快递类型"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        history = ExpressQuest.objects.create(user_id=user.id, file=file, is_enter=is_enter, line_number=nrows)
        if is_enter == 1:
            # execute_express_quest({'id': history.id})
            execute_express_quest.delay({'id': history.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)

    def receive_goods(self, request):
        user = self.request.iser
        order_id = self.request.data.get('order_id', None)
        if not order_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            order = self.get_queryset().get(id=order_id)
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        if order.state != 6:
            return Response({'error': '订单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        order.state = 14
        order.save()
        # OrderAdvance.objects.create(
        #     order_id=order.id,
        #     status=4,
        #     remark='超管确认收货',
        #     user_id=user.id
        # )
        try:
            # if order.is_profit_sharing in [1, 3]:
            #     if order.is_profit_sharing == 1:
            #         out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
            #         body = {
            #             "program_id": "202316837141378884",
            #             "shop_id": order.merchant.ruiyinxinMerchantID,
            #             "order_id": out_trade_no,
            #             "out_trade_no": order.orderNum,
            #             "is_profit": "0"
            #         }
            #         result = ryx_profitsharing_order(body)
            #     else:
            #         out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
            #         body = {
            #             "program_id": "202316837141378884",
            #             "shop_id": order.merchant.ruiyinxinMerchantID,
            #             "order_id": out_trade_no,
            #             "out_trade_no": order.orderNum,
            #             "is_profit": "2"
            #         }
            #         result = ryx_profitsharing_order(body)
            #     profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
            #     if not profitorder:
            #         profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
            #     if result['result_code'] == 'success':
            #         order.is_profit_sharing = 2
            #         profitorder.profit_order_id = result['profit_order_id']
            #         profitorder.profit_time = datetime.datetime.now()
            #         profitorder.save()
            #         receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
            #         bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species).first()
            #         if not bill:
            #             bill_data = {}
            #             bill_data['merchant_id'] = order.merchant_id
            #             bill_data['receipts_time'] = receipts_time
            #             bill_data['amount'] = order.total_price - order.freight
            #             bill_data['commission'] = order.freight
            #             bill_data['level_id'] = order.merchant.level_id
            #             bill_data['payable_amount'] = order.total_price - order.freight
            #             bill_data['channel_id'] = 3
            #             bill_data['bill_type'] = order.order_species
            #             bill, create = MerchantBill.objects.get_or_create(defaults=bill_data, receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species)
            #         else:
            #             bill.amount = bill.amount + order.total_price
            #             bill.commission = bill.commission + order.freight
            #             bill.payable_amount = bill.payable_amount + order.total_price - order.freight
            #             bill.save()
            #         order.payable_settled_amount = order.total_price - order.freight
            #         order.bill_id = bill.id
            #         order.save()
            #     else:
            #         profitorder.error_msg = result['error_msg']
            #         profitorder.profit_status = 2
            #         profitorder.save()
            if order.is_profit_sharing == 1:
                out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                body = {
                    "program_id": "202316837141378884",
                    "shop_id": order.merchant.ruiyinxinMerchantID,
                    "order_id": out_trade_no,
                    "out_trade_no": order.orderNum,
                    "is_profit": "0"
                }
                result = ryx_profitsharing_order(body)
                profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                if not profitorder:
                    profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                if result['result_code'] == 'success':
                    order.is_profit_sharing = 2
                    order.save()
                    profitorder.profit_order_id = result['profit_order_id']
                    profitorder.profit_time = datetime.datetime.now()
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=5,
                        remark='超管确认收货,解冻资金',
                        user_id=user.id
                    )
                    receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                    bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id,
                                                       bill_type=order.order_species).first()
                    if not bill:
                        bill_data = {}
                        bill_data['merchant_id'] = order.merchant_id
                        bill_data['receipts_time'] = receipts_time
                        bill_data['amount'] = order.total_price
                        bill_data['commission'] = order.freight
                        bill_data['level_id'] = order.merchant.level_id
                        bill_data['payable_amount'] = order.total_price - order.freight
                        bill_data['channel_id'] = 3
                        bill_data['bill_type'] = order.order_species
                        bill, create = MerchantBill.objects.get_or_create(defaults=bill_data,
                                                                          receipts_time=receipts_time,
                                                                          merchant_id=order.merchant_id,
                                                                          bill_type=order.order_species)
                    else:
                        bill.amount = bill.amount + order.total_price
                        bill.commission = bill.commission + order.freight
                        bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                        bill.save()
                    order.payable_settled_amount = order.total_price - order.freight
                    order.bill_id = bill.id
                    order.save()
                else:
                    order.is_profit_sharing = 2
                    order.save()
                    profitorder.error_msg = result['error_msg']
                    profitorder.profit_status = 2
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=4,
                        remark='超管确认收货,解冻资金出错',
                        user_id=user.id
                    )
            elif order.is_profit_sharing == 3:
                out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                body = {
                    "program_id": "202316837141378884",
                    "shop_id": order.merchant.ruiyinxinMerchantID,
                    "order_id": out_trade_no,
                    "out_trade_no": order.orderNum,
                    "is_profit": "2"
                }
                result = ryx_profitsharing_order(body)
                profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                if not profitorder:
                    profitorder, create = OrderProfitSharing.objects.get_or_create(
                        defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                if result['result_code'] == 'success':
                    order.is_profit_sharing = 4
                    order.save()
                    profitorder.profit_order_id = result['profit_order_id']
                    profitorder.profit_time = datetime.datetime.now()
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=5,
                        remark='超管确认收货,解冻资金',
                        user_id=user.id
                    )
                    receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                    bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id,
                                                       bill_type=order.order_species).first()
                    if not bill:
                        bill_data = {}
                        bill_data['merchant_id'] = order.merchant_id
                        bill_data['receipts_time'] = receipts_time
                        bill_data['amount'] = order.total_price
                        bill_data['commission'] = order.freight
                        bill_data['level_id'] = order.merchant.level_id
                        bill_data['payable_amount'] = order.total_price - order.freight
                        bill_data['channel_id'] = 3
                        bill_data['bill_type'] = order.order_species
                        bill, create = MerchantBill.objects.get_or_create(defaults=bill_data,
                                                                          receipts_time=receipts_time,
                                                                          merchant_id=order.merchant_id,
                                                                          bill_type=order.order_species)
                    else:
                        bill.amount = bill.amount + order.total_price
                        bill.commission = bill.commission + order.freight
                        bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                        bill.save()
                    order.payable_settled_amount = order.total_price - order.freight
                    order.bill_id = bill.id
                    order.save()
                else:
                    order.is_profit_sharing = 4
                    order.save()
                    profitorder.error_msg = result['error_msg']
                    profitorder.profit_status = 2
                    profitorder.save()
                    OrderAdvance.objects.create(
                        order_id=order.id,
                        status=4,
                        remark='超管确认收货,解冻资金出错',
                        user_id=user.id
                    )
        except:
            pass
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def update_orderaddress(self, request):
        order_id = self.request.data.get('order_id', None)
        province = self.request.data.get('province', None)
        city = self.request.data.get('city', None)
        district = self.request.data.get('district', None)
        mobile = self.request.data.get('mobile', None)
        receiver = self.request.data.get('receiver', None)
        place = self.request.data.get('place', None)
        if not order_id:
            return Response({'error': '请上传订单id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            order = self.get_queryset().get(id=order_id)
            orderaddress = order.orderaddress
        except:
            return Response({'error': '无效订单id'}, status=status.HTTP_400_BAD_REQUEST)
        if order.order_type != 5:
            return Response({'error': '订单类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        if order.state == 14:
            return Response({'error': '已完成订单不能修改配送地址'}, status=status.HTTP_400_BAD_REQUEST)
        if not all([province, city, district, mobile, receiver, place]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        orderaddress.province = province
        orderaddress.city = city
        orderaddress.district = district
        orderaddress.mobile = mobile
        orderaddress.receiver = receiver
        orderaddress.place = place
        orderaddress.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def use_coupon(self, request):
        user = self.request.iser
        order_id = self.request.data.get('order_id', None)
        if not all([order_id]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            order = OrderInfo.objects.get(id=order_id)
        except:
            return Response({'error': '无效订单号'}, status=status.HTTP_400_BAD_REQUEST)
        if order.state != 2:
            return Response({'error': '订单已无未核销商品,请刷新核实'}, status=status.HTTP_400_BAD_REQUEST)
        if order.order_type == 3:
            return Response({'error': '此核销码为兑换券类型,请在对应渠道使用'}, status=status.HTTP_400_BAD_REQUEST)
        goodscoupons_set = GoodsCoupons.objects.filter(order_id=order_id, status=1)
        for goodscoupons in goodscoupons_set:
            goodscoupons.status = 2
            goodscoupons.use_time = datetime.datetime.now()
            goodscoupons.use_user_id = user.id
            goodscoupons.save()
        data = {}
        goodscoupons_set = GoodsCoupons.objects.filter(order_id=order_id, status=1)
        if not goodscoupons_set:
            order.state = 3
            order.use_time = datetime.datetime.now()
            order.save()
            if order.order_type in [1, 7]:
                try:
                    if order.is_profit_sharing == 1:
                        out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                        body = {
                            "program_id": "202316837141378884",
                            "shop_id": order.merchant.ruiyinxinMerchantID,
                            "order_id": out_trade_no,
                            "out_trade_no": order.orderNum,
                            "is_profit": "0"
                        }
                        result = ryx_profitsharing_order(body)
                        profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                        if not profitorder:
                            profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                        if result['result_code'] == 'success':
                            order.is_profit_sharing = 2
                            order.save()
                            profitorder.profit_order_id = result['profit_order_id']
                            profitorder.profit_time = datetime.datetime.now()
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=5,
                                remark='管理员核销订单,解冻资金',
                                user_id=user.id
                            )
                            receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                            bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species).first()
                            if not bill:
                                bill_data = {}
                                bill_data['merchant_id'] = order.merchant_id
                                bill_data['receipts_time'] = receipts_time
                                bill_data['amount'] = order.total_price
                                bill_data['commission'] = order.freight
                                bill_data['level_id'] = order.merchant.level_id
                                bill_data['payable_amount'] = order.total_price - order.freight
                                bill_data['channel_id'] = 3
                                bill_data['bill_type'] = order.order_species
                                bill, create = MerchantBill.objects.get_or_create(defaults=bill_data, receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species)
                            else:
                                bill.amount = bill.amount + order.total_price
                                bill.commission = bill.commission + order.freight
                                bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                                bill.save()
                            order.payable_settled_amount = order.total_price - order.freight
                            order.bill_id = bill.id
                            order.save()
                        else:
                            order.is_profit_sharing = 2
                            order.save()
                            profitorder.error_msg = result['error_msg']
                            profitorder.profit_status = 2
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=4,
                                remark='管理员核销订单,解冻资金出错',
                                user_id=user.id
                            )
                    elif order.is_profit_sharing == 3:
                        out_trade_no = f'{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                        body = {
                            "program_id": "202316837141378884",
                            "shop_id": order.merchant.ruiyinxinMerchantID,
                            "order_id": out_trade_no,
                            "out_trade_no": order.orderNum,
                            "is_profit": "2"
                        }
                        result = ryx_profitsharing_order(body)
                        profitorder = OrderProfitSharing.objects.filter(order_id=order.id).first()
                        if not profitorder:
                            profitorder, create = OrderProfitSharing.objects.get_or_create(defaults={'order_id': order.id, "out_trade_no": out_trade_no}, order_id=order.id)
                        if result['result_code'] == 'success':
                            order.is_profit_sharing = 4
                            order.save()
                            profitorder.profit_order_id = result['profit_order_id']
                            profitorder.profit_time = datetime.datetime.now()
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=5,
                                remark='管理员核销订单,解冻资金',
                                user_id=user.id
                            )
                            receipts_time = datetime.datetime.now().strftime('%Y-%m-%d')
                            bill = MerchantBill.objects.filter(receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species).first()
                            if not bill:
                                bill_data = {}
                                bill_data['merchant_id'] = order.merchant_id
                                bill_data['receipts_time'] = receipts_time
                                bill_data['amount'] = order.total_price
                                bill_data['commission'] = order.freight
                                bill_data['level_id'] = order.merchant.level_id
                                bill_data['payable_amount'] = order.total_price - order.freight
                                bill_data['channel_id'] = 3
                                bill_data['bill_type'] = order.order_species
                                bill, create = MerchantBill.objects.get_or_create(defaults=bill_data, receipts_time=receipts_time, merchant_id=order.merchant_id, bill_type=order.order_species)
                            else:
                                bill.amount = bill.amount + order.total_price
                                bill.commission = bill.commission + order.freight
                                bill.payable_amount = bill.payable_amount + order.total_price - order.freight
                                bill.save()
                            order.payable_settled_amount = order.total_price - order.freight
                            order.bill_id = bill.id
                            order.save()
                        else:
                            order.is_profit_sharing = 4
                            order.save()
                            profitorder.error_msg = result['error_msg']
                            profitorder.profit_status = 2
                            profitorder.save()
                            OrderAdvance.objects.create(
                                order_id=order.id,
                                status=4,
                                remark='管理员核销订单,解冻资金出错',
                                user_id=user.id
                            )
                except Exception as e:
                    logger.error(e)
                    pass
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        coupon_arr = []
        coupon_list = []
        for goodscoupons in goodscoupons_set:
            coupon_arr.append(goodscoupons.coupons_id)
            coupon_list.append({
                'goods_name': goodscoupons.goods.name,
                'price': goodscoupons.goods.price,
                'count': 1
            })
        data['request_date'] = {'coupon_arr': coupon_arr, 'order_id': order.id}
        data['coupon_list'] = coupon_list
        return Response(data, status=status.HTTP_200_OK)


class OrderInfoGROUPModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    filter_class = OrderInfoGROUPFilter

    def get_queryset(self):
        queryset = OrderInfoGROUP.objects.all()
        search = self.request.query_params.get('search', None)
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": ['_id'],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            body['query']['bool']['must_not'].append({"wildcard": {"text": "*vm*"}})
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # return Response(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            queryset = queryset.filter(order_id__in=id_list)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return OrderInfoGROUPListModelSerializer
        else:
            return OrderInfoGROUPRetrieveModelSerializer

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.group_status != 1:
            return Response({'error': '拼团状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        group_order_num = OrderInfo.objects.filter(state=15, group_id=instance.id).count()
        instance.group_order_num = group_order_num
        instance.save()
        group = OrderInfoGROUP.objects.get(id=instance.id)
        cancel_group_order(group)
        return Response({'sucess': '操作成功'}, status=status.HTTP_200_OK)


class OrderSchengleModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('merchant__name', 'ordergoods__goods__name', 'customer_user__mobile')
    filter_class = OrderSchengleFilter

    def get_serializer_class(self):
        if self.action == 'list':
            return OrderSchengleListModelSerializer
        else:
            return OrderSchengleRetrieveModelSerializer

    def get_queryset(self):
        search = self.request.query_params.get('search', None)
        queryset = OrderSchengle.objects.all()
        if search:
            es = Elasticsearch(["http://159.75.55.19:9200/"])
            body = {
                "query": {
                    "bool": {
                        "must": [],
                        "must_not": [],
                        "should": []
                    }
                },
                "from": 0,
                "size": 10000,
                "sort": [{"_id": "asc"}],
                "aggs": {}
            }
            if not search.isdigit():
                filter_data = {
                    "match_phrase_prefix": {
                        "text": search
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            else:
                filter_data = {
                    "wildcard": {
                        "text": f'*{search}*'
                    }
                }
                body['query']['bool']['must'].append(filter_data)
            filter_data = {
                "wildcard": {
                    "text": '6013315788'
                }
            }
            body['query']['bool']['should'].append(filter_data)
            result = es.search(index="equity_mall_order", doc_type="modelresult", body=body)
            # print(result)
            hits = result['hits']['hits']
            id_list = []
            for order in hits:
                id_list.append(order['_id'])
            queryset = queryset.filter(Q(order_id__in=id_list) | Q(orderId__contains=search))
        return queryset.order_by('-id')

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        user = self.request.iser
        remark = self.request.data.get('remark', None)
        if user.id not in [1, 12105, 9297, 65139]:
            return Response({'error': '您无权限操作'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.order_state != 2:
            return Response({'error': '活动状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        today = datetime.datetime.now().strftime("%Y-%m-%d")
        if instance.create_time.strftime("%Y-%m-%d") != today:
            return Response({'error': '订单已过了退款时效,不能操作'}, status=status.HTTP_400_BAD_REQUEST)
        data = {}
        data['source'] = 'GDYZQY'
        data['orderId'] = instance.orderId
        result = schengle.cancel_order(data)
        if result['resultCode'] != 'success':
            return Response({'error': f'取消订单失败,{result["resultDesc"]}'}, status=status.HTTP_400_BAD_REQUEST)
        instance.order_state = 6
        instance.cancel_time = datetime.datetime.now()
        instance.cancel_user_id = user.id
        instance.save()
        order = instance.order
        url = 'https://api.gdwxyf.com/api/refund.do'
        datetime_now = datetime.datetime.now()
        order_id = f'{datetime_now.strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
        body = {
            "program_id": "202316837141378884",
            "shop_id": order.merchant.ruiyinxinMerchantID,
            "refund_order_id": order_id,
            "order_id": order.orderNum
        }
        key = '6715C7D4435343BA0459EAEC2334D81B'
        sign = get_sign(body, key)
        body['sign'] = sign
        response = requests.post(url=url, json=body)
        result = response.json()
        if result['result_code'] == 'success':
            refund_order_data = {}
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_transaction_id'] = result['refund_transaction_id']
            refund_order_data['plat_refund_order_id'] = result['plat_refund_order_id']
            refund_order_data['refund_money'] = result['refund_money']
            refund_order_data['refund_procedure_money'] = result['commission']
            refund_order_data['refund_time'] = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            order.state = 8
            order.refund_money = order.realpay_amount
            order.out_refund_no = order_id
            order.refund_id = result['refund_transaction_id']
            order.save()
            try:
                after_sale = AfterSaleApplicationForm.objects.filter(order_id=order.id, form_status=1).last()
                if after_sale:
                    after_sale.form_status = 2
                    after_sale.refund_time = datetime.datetime.strptime(result['refund_end_time'], '%Y%m%d%H%M%S')
                    after_sale.save()
            except:
                pass
            bill = order.bill
            bill.amount = bill.amount - order.total_price
            bill.commission = bill.commission - order.freight
            bill.payable_amount = bill.payable_amount - order.total_price + order.freight
            bill.save()
            order.payable_settled_amount = 0
            order.bill_id = None
            order.save()
            ordergoods_set = order.ordergoods_set.all()
            for ordergoods in ordergoods_set:
                goods = ordergoods.goods
                launched_goods = ordergoods.launched_goods
                count = ordergoods.count
                origin_sales = goods.sales
                origin_remain_stock = goods.remain_stock
                new_sales = origin_sales - count
                new_remain_stock = origin_remain_stock + count
                goods.sales = new_sales
                goods.remain_stock = new_remain_stock
                goods.save()
                if launched_goods:
                    launched_origin_stock = launched_goods.stock
                    launched_origin_sales = launched_goods.sales
                    launched_new_stock = launched_origin_stock + count
                    launched_new_sales = launched_origin_sales - count
                    launched_goods.stock = launched_new_stock
                    launched_goods.sales = launched_new_sales
                    launched_goods.save()
                else:
                    origin_stock = goods.stock
                    new_stock = origin_stock + count
                    goods.stock = new_stock
                    goods.save()
            return Response({'success': '退款成功'}, status=status.HTTP_200_OK)
        else:
            refund_order_data = {}
            refund_order_data['refund_status'] = 2
            refund_order_data['operator_role'] = user.role_id
            refund_order_data['order_id'] = order.id
            refund_order_data['user_id'] = user.id
            refund_order_data['return_msg'] = result['error_msg']
            refund_order_data['refund_order_id'] = order_id
            refund_order_data['refund_time'] = datetime_now
            refund_order_data['remark'] = remark
            RefundOrderInfo.objects.create(**refund_order_data)
            return Response({'error': f'取消订单成功,退款失败,原因:{result["error_msg"]}'}, status=status.HTTP_400_BAD_REQUEST)

